Menu
I need to update the primary key for a record but it's also the foreign key in two other tables. And I need the updated primary key to be reflected in the child tables as well.
Here is my query and the error:
begin tran update question set questionparent = 10000, questionid= 10005 where questionid = 11000;Error 9/4/2009 10:04:49 AM 0:00:00.000 SQL Server Database Error: The UPDATE statement conflicted with the REFERENCE constraint "FK_GoalRequirement_Question". The conflict occurred in database "numgmttest", table "dbo.GoalRequirement", column 'QuestionID'. 14 0
I don't remember how to go about doing this so that's why I'm here. Any help?
730
The FOREIGN KEY constraint differs from the PRIMARY KEY constraint in that, you can create only one PRIMARY KEY per each table, with the ability to create multiple FOREIGN KEY constraints in each table by referencing multiple parent table.
A table can have only one Primary Key. A table can have any number of Foreign Keys. The primary key is unique and Not Null.
To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.
Login to the SQL Server using SQL Server Management Studio, Navigate to the Keys folder in the child table. Right click on the Keys folder and select New Foreign Key. Edit table and columns specification by clicking … as shown in the below image. Select the parent table and the primary key column in the parent table.
Are your relationships using
ON UPDATE CASCADE If they are then changing the key in the primary table will update the foreign keys.
e.g.
ALTER TABLE Books ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID) ON UPDATE CASCADE If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
