SQL Change Id values in a table and update all dependent records in other tables












0















Is it possible to change values of ID column in a table and at the same time update all dependent records in other tables?



Example Data:



Table : Document                        =>  should be:
Id Code Title Id Title
------------------------------------ ------------------------------------
1 112512 "Some Title" 112512 "Some Title"
2 123234 "Some Title" 123234 "Some Title"
3 234321 "Some Title" 234321 "Some Title"

Table : Person
Id Name DocumentID
-----------------------------------
290 "Roberto" 1 ---> should change to 112512
291 "Roberta" 3 ---> should change to 234321


I'm using entity framework to access SQLServer databse and I think that managing forms and user inputs will be much easier with the above change. the Migration for structure is done. now I need a SQL Command for data migration. I Also tried migration data manually:



var Pers = unitOfWork.PersonRep.GetAll().ToList();
foreach (var person in persons)
{
var doc = person.Document;
int code = int.Parse(doc.Code);
doc.ID = code;
person.DocumnetId = code;
}
unitOfWork.Complete();


Migration failed with this error:




The property 'ID' is part of the object's key information and cannot
be modified.











share|improve this question























  • Can you add the table definitions for document and person please,

    – P.Salmon
    Nov 25 '18 at 13:15











  • @P.Salmon definitions are pretty standard. Id is Primary Key in both tables. DocumentId is ForeignKey. Also PK of Document table is set to DatabaseGeneratedOption.None

    – roozbeh S
    Nov 25 '18 at 13:25
















0















Is it possible to change values of ID column in a table and at the same time update all dependent records in other tables?



Example Data:



Table : Document                        =>  should be:
Id Code Title Id Title
------------------------------------ ------------------------------------
1 112512 "Some Title" 112512 "Some Title"
2 123234 "Some Title" 123234 "Some Title"
3 234321 "Some Title" 234321 "Some Title"

Table : Person
Id Name DocumentID
-----------------------------------
290 "Roberto" 1 ---> should change to 112512
291 "Roberta" 3 ---> should change to 234321


I'm using entity framework to access SQLServer databse and I think that managing forms and user inputs will be much easier with the above change. the Migration for structure is done. now I need a SQL Command for data migration. I Also tried migration data manually:



var Pers = unitOfWork.PersonRep.GetAll().ToList();
foreach (var person in persons)
{
var doc = person.Document;
int code = int.Parse(doc.Code);
doc.ID = code;
person.DocumnetId = code;
}
unitOfWork.Complete();


Migration failed with this error:




The property 'ID' is part of the object's key information and cannot
be modified.











share|improve this question























  • Can you add the table definitions for document and person please,

    – P.Salmon
    Nov 25 '18 at 13:15











  • @P.Salmon definitions are pretty standard. Id is Primary Key in both tables. DocumentId is ForeignKey. Also PK of Document table is set to DatabaseGeneratedOption.None

    – roozbeh S
    Nov 25 '18 at 13:25














0












0








0








Is it possible to change values of ID column in a table and at the same time update all dependent records in other tables?



Example Data:



Table : Document                        =>  should be:
Id Code Title Id Title
------------------------------------ ------------------------------------
1 112512 "Some Title" 112512 "Some Title"
2 123234 "Some Title" 123234 "Some Title"
3 234321 "Some Title" 234321 "Some Title"

Table : Person
Id Name DocumentID
-----------------------------------
290 "Roberto" 1 ---> should change to 112512
291 "Roberta" 3 ---> should change to 234321


I'm using entity framework to access SQLServer databse and I think that managing forms and user inputs will be much easier with the above change. the Migration for structure is done. now I need a SQL Command for data migration. I Also tried migration data manually:



var Pers = unitOfWork.PersonRep.GetAll().ToList();
foreach (var person in persons)
{
var doc = person.Document;
int code = int.Parse(doc.Code);
doc.ID = code;
person.DocumnetId = code;
}
unitOfWork.Complete();


Migration failed with this error:




The property 'ID' is part of the object's key information and cannot
be modified.











share|improve this question














Is it possible to change values of ID column in a table and at the same time update all dependent records in other tables?



Example Data:



Table : Document                        =>  should be:
Id Code Title Id Title
------------------------------------ ------------------------------------
1 112512 "Some Title" 112512 "Some Title"
2 123234 "Some Title" 123234 "Some Title"
3 234321 "Some Title" 234321 "Some Title"

Table : Person
Id Name DocumentID
-----------------------------------
290 "Roberto" 1 ---> should change to 112512
291 "Roberta" 3 ---> should change to 234321


I'm using entity framework to access SQLServer databse and I think that managing forms and user inputs will be much easier with the above change. the Migration for structure is done. now I need a SQL Command for data migration. I Also tried migration data manually:



var Pers = unitOfWork.PersonRep.GetAll().ToList();
foreach (var person in persons)
{
var doc = person.Document;
int code = int.Parse(doc.Code);
doc.ID = code;
person.DocumnetId = code;
}
unitOfWork.Complete();


Migration failed with this error:




The property 'ID' is part of the object's key information and cannot
be modified.








c# sql-server entity-framework ef-migrations






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 25 '18 at 13:07









roozbeh Sroozbeh S

8381411




8381411













  • Can you add the table definitions for document and person please,

    – P.Salmon
    Nov 25 '18 at 13:15











  • @P.Salmon definitions are pretty standard. Id is Primary Key in both tables. DocumentId is ForeignKey. Also PK of Document table is set to DatabaseGeneratedOption.None

    – roozbeh S
    Nov 25 '18 at 13:25



















  • Can you add the table definitions for document and person please,

    – P.Salmon
    Nov 25 '18 at 13:15











  • @P.Salmon definitions are pretty standard. Id is Primary Key in both tables. DocumentId is ForeignKey. Also PK of Document table is set to DatabaseGeneratedOption.None

    – roozbeh S
    Nov 25 '18 at 13:25

















Can you add the table definitions for document and person please,

– P.Salmon
Nov 25 '18 at 13:15





Can you add the table definitions for document and person please,

– P.Salmon
Nov 25 '18 at 13:15













@P.Salmon definitions are pretty standard. Id is Primary Key in both tables. DocumentId is ForeignKey. Also PK of Document table is set to DatabaseGeneratedOption.None

– roozbeh S
Nov 25 '18 at 13:25





@P.Salmon definitions are pretty standard. Id is Primary Key in both tables. DocumentId is ForeignKey. Also PK of Document table is set to DatabaseGeneratedOption.None

– roozbeh S
Nov 25 '18 at 13:25












2 Answers
2






active

oldest

votes


















0














It looks like id is a primary key, as part of EF limitation you can't change pk you can either add another id for example (id_pk) and set id_pk as pk and this is how you can change you id or just delete insert.



in general if your id keeps changing you would want to use id_pk






share|improve this answer































    0














    Your best bet (if you need to keep your data) would be to recreate the FKs in the DB with the ON UPDATE CASCADE option (see documentation for more details). Then updating the values through standard SQL UPDATE statement and then recreating the FKs without the option.



    The recreation (drop & create) of the FKs makes sure, that when you run the UPDATE statement on the Document table, all FKs pointing to it will be updated automatically (if you have more FKs pointing to Document.ID you should recreate all of them using the option). The second recreation of the FKs is just there to set the things the same way as they were before - I'm not sure what role it does play for code-first and/or migrations. Also you would need to make sure, that you keep the names of FKs the same (again not sure if needed).



    This of course assumes, you feel comfortable about using direct SQL as it's ignoring the migrations part altogether.






    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467761%2fsql-change-id-values-in-a-table-and-update-all-dependent-records-in-other-tables%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      It looks like id is a primary key, as part of EF limitation you can't change pk you can either add another id for example (id_pk) and set id_pk as pk and this is how you can change you id or just delete insert.



      in general if your id keeps changing you would want to use id_pk






      share|improve this answer




























        0














        It looks like id is a primary key, as part of EF limitation you can't change pk you can either add another id for example (id_pk) and set id_pk as pk and this is how you can change you id or just delete insert.



        in general if your id keeps changing you would want to use id_pk






        share|improve this answer


























          0












          0








          0







          It looks like id is a primary key, as part of EF limitation you can't change pk you can either add another id for example (id_pk) and set id_pk as pk and this is how you can change you id or just delete insert.



          in general if your id keeps changing you would want to use id_pk






          share|improve this answer













          It looks like id is a primary key, as part of EF limitation you can't change pk you can either add another id for example (id_pk) and set id_pk as pk and this is how you can change you id or just delete insert.



          in general if your id keeps changing you would want to use id_pk







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 25 '18 at 13:19









          Daniel RapaportDaniel Rapaport

          176116




          176116

























              0














              Your best bet (if you need to keep your data) would be to recreate the FKs in the DB with the ON UPDATE CASCADE option (see documentation for more details). Then updating the values through standard SQL UPDATE statement and then recreating the FKs without the option.



              The recreation (drop & create) of the FKs makes sure, that when you run the UPDATE statement on the Document table, all FKs pointing to it will be updated automatically (if you have more FKs pointing to Document.ID you should recreate all of them using the option). The second recreation of the FKs is just there to set the things the same way as they were before - I'm not sure what role it does play for code-first and/or migrations. Also you would need to make sure, that you keep the names of FKs the same (again not sure if needed).



              This of course assumes, you feel comfortable about using direct SQL as it's ignoring the migrations part altogether.






              share|improve this answer




























                0














                Your best bet (if you need to keep your data) would be to recreate the FKs in the DB with the ON UPDATE CASCADE option (see documentation for more details). Then updating the values through standard SQL UPDATE statement and then recreating the FKs without the option.



                The recreation (drop & create) of the FKs makes sure, that when you run the UPDATE statement on the Document table, all FKs pointing to it will be updated automatically (if you have more FKs pointing to Document.ID you should recreate all of them using the option). The second recreation of the FKs is just there to set the things the same way as they were before - I'm not sure what role it does play for code-first and/or migrations. Also you would need to make sure, that you keep the names of FKs the same (again not sure if needed).



                This of course assumes, you feel comfortable about using direct SQL as it's ignoring the migrations part altogether.






                share|improve this answer


























                  0












                  0








                  0







                  Your best bet (if you need to keep your data) would be to recreate the FKs in the DB with the ON UPDATE CASCADE option (see documentation for more details). Then updating the values through standard SQL UPDATE statement and then recreating the FKs without the option.



                  The recreation (drop & create) of the FKs makes sure, that when you run the UPDATE statement on the Document table, all FKs pointing to it will be updated automatically (if you have more FKs pointing to Document.ID you should recreate all of them using the option). The second recreation of the FKs is just there to set the things the same way as they were before - I'm not sure what role it does play for code-first and/or migrations. Also you would need to make sure, that you keep the names of FKs the same (again not sure if needed).



                  This of course assumes, you feel comfortable about using direct SQL as it's ignoring the migrations part altogether.






                  share|improve this answer













                  Your best bet (if you need to keep your data) would be to recreate the FKs in the DB with the ON UPDATE CASCADE option (see documentation for more details). Then updating the values through standard SQL UPDATE statement and then recreating the FKs without the option.



                  The recreation (drop & create) of the FKs makes sure, that when you run the UPDATE statement on the Document table, all FKs pointing to it will be updated automatically (if you have more FKs pointing to Document.ID you should recreate all of them using the option). The second recreation of the FKs is just there to set the things the same way as they were before - I'm not sure what role it does play for code-first and/or migrations. Also you would need to make sure, that you keep the names of FKs the same (again not sure if needed).



                  This of course assumes, you feel comfortable about using direct SQL as it's ignoring the migrations part altogether.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 25 '18 at 18:06









                  MartyMarty

                  1846




                  1846






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467761%2fsql-change-id-values-in-a-table-and-update-all-dependent-records-in-other-tables%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                      Calculate evaluation metrics using cross_val_predict sklearn

                      Insert data from modal to MySQL (multiple modal on website)