SQL Server update primary key that's also a foreign key in two tables
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?
- disable enforcing FK constraints temporarily (see here or here)
- update your PK
- update your FKs
- enable back enforcing FK constraints
do it all within a transaction and make sure that if transaction fails, you roll it back properly and still enforce the FK constraints back.
But... why do you need to change a PK? I hope this is an action that is executed rarely (legacy data import or something like that).
How to update primary key, Nearly 10 table are referencing the Primary key of that table as Foreign Key. Now i want to update value of Primary Key column. Please help Using SQL Server Management Studio To modify a primary key. Open the Table Designer for the table whose primary key you want to modify, right-click in the Table Designer, and choose Indexes/Keys from the shortcut menu. In the Indexes/Keys dialog box, select the primary key index from the Selected Primary/Unique Key or Index list. Complete an action from the following table:
Are your relationships using
ON UPDATE CASCADE
If they are then changing the key in the primary table will update the foreign keys.
ALTER TABLE Books ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID) ON UPDATE CASCADE
Update Primary Key column in Sql Server - MSDN, To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:. Once a record is created with a primary key, you really shouldn't be updating the primary key. The point of a PK is that it uniquely identifies that row.
If you would like to set the Cascade rule graphically then Set Cascade Rule on SQL Management Studio
- Open table in design mode
- Click Relationship button from top toolbar
- Select the required FK relations (one by one)
- Right Side - Expand INSERT or UPDATE Specification
- Change the UPDATE Rule to - Cascade
Close and Save, Done!
(Tried on SQL 2008)
SQL PRIMARY KEY Constraint, To modify a primary key, execute an ALTER TABLEDROP PRIMARY KEY statement to drop the existing primary key, and then execute an ALTER TABLE A primary key can only be added to columns that are defined as NOT NULL. If you try to add a primary key to a column that is nullable, you’ll get an error. If you try to add a primary key to a column that is nullable, you’ll get an error.
As I'm not too confident disabling FK constraints, I prefer too :
- Duplicate the row with the old PK with one with the new PK
- Update the FKs
- Delete the row with the old PK
Advantage : No constraint violated during the process.
Creating and modifying a primary key (SQL), Sometimes we have no option other than to change the Primary Key and its The task may also call for us to span databases in the server, so we must Fortunately, if we are using Data Masker we don't have to create SQL SQL Server also automatically creates a unique clustered index (or a non-clustered index if specified as such) when you create a primary key. SQL Server PRIMARY KEY constraint examples. The following example creates a table with a primary key that consists of one column:
go to foreign Key Relations of each child tables and on Insert and Update specification change delete and update rules to cascade. this may help u little bit
How do I change a Primary Key value and all associated Foreign , Best practice - Update Primary Keys · sql-server primary-key. What's you opinion about Update a primary Key in SQL? I have a "link SQL Server also automatically creates a unique clustered index (or a non-clustered index if specified as such) when you create a primary key. Method-1: If the primary key consists of only one column, you can define use PRIMARY KEY constraint as a column constraint:
Best practice - Update Primary Keys, Learn how to create, change, drop, disable, and enable a primary key in SQL Server (Transact-SQL) with syntax and examples. In SQL Server (Transact-SQL), T-SQL: Add a Primary key to existing table using Alter Table. You can use the ALTER statement to create a primary key. However, the primary key can only be created on columns that are defined as NOT NULL. You cannot create a primary key on a column that allows NULLs. If you need to do, you have to drop and recreate the table. Here is the syntax:
SQL Server: Primary Keys, To ensure that the table is partitioned correctly, you must create a partition on the secondary index, or drop the secondary index. Tip: To change an existing primary 4 SQL SERVER guncelleme update silme delete - Duration: 5:10. Differences between primary key and unique key - SQL Server Interview Questions - Duration: 6:07.
how to update primary key which is auto inrement in sql, To update SQL Server 2019 on Linux to the latest CU, you must first have the Cumulative Update repository configured. Then, update your SQL Server packages by using the appropriate platform-specific update command. For installation instructions and direct links to the CU package downloads, see the SQL Server 2019 Release Notes.