SQL Server update primary key that's also a foreign key in two tables

sql server add primary key to existing table with data
how to update primary key value in mysql
cannot update identity column
sql update
sql update primary key and foreign key
update primary key mysql
data type for primary key in sql server
how to disable primary key sql

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?


You may:

  1. disable enforcing FK constraints temporarily (see here or here)
  2. update your PK
  3. update your FKs
  4. 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.

e.g.

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

  1. Open table in design mode
  2. Click Relationship button from top toolbar
  3. Select the required FK relations (one by one)
  4. Right Side - Expand INSERT or UPDATE Specification
  5. 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 :

  1. Duplicate the row with the old PK with one with the new PK
  2. Update the FKs
  3. 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.