Primary Key & Related Tables

[jpiper]

Is there a way to modify the text of a primary key and not have it delete all the following rows in other tables related to it?

[GaryMazzone]

No you can not change the value of a primary key and not have it effect the child tables. In fact the Primay key field should never be modified.

[kaliman79912]

If you absolutely need to change it, Try creating a procedure where all the data is copied with all childs using the new primary key, then delete the original.

[jpiper]

I'm with you guys, can you suggest something for me to try differently then? I have a primary key "ProjectNumber" linked one to many to each "ReportNumber" related to that project on another table. If the user someday needs to change the project number... say they set up the project wrong or the client needs it to be different, is there a way to be able to do it?

[kaliman79912]

Of course programatically you could go about this, but I suggest you keep with the norm in relational databases and prohibit this. Most systems, even the morst prestigios do not allow the ID of items in the main table to be modified.
If needed be, create a new project.
Anyway, I will go back to this thread with a way to do it.

[kareninstructor]

A quick way to learn from pre-define models
http://www.databaseanswers.org/data_models/index.htm

[jpiper]

Don't sweat it kaliman, not important enough to try. I was hoping there was an "easy" way to do it, like how it automatically adds the primary key to each record I was hoping for some kind of miracle. I'll just have them create a new project and their punishment for being lazy will be to update all the past records into the new number... if they don't ignore it then it should only be a couple reports before project number error is caught. I have added some validation for the structure/syntax of how our project numbers are created so the user will almost have to try to get it wrong. Just hoping to create flexibility.

Kevin, excellent source as always.

Thanks guys for looking into it for me!