How to update primary key
Here is my problem - I have 2 tables:
- WORKER, with columns
|ID|OTHER_STAF|, where ID is primar开发者_开发问答y key - FIRM, with columns
|FPK|ID|SOMETHING_ELSE|, where combination FPK and ID make primary key, and also ID is a foreign key referenced to WORKER.ID (not null, and must have same value as in WORKER).
I want to make stored procedure UPDATE_ID_WORKER, where I would like to change the value of specific ID in WORKER, and also in all instances of specific value of ID in FIRM.
You shouldn't really do this but insert in a new record instead and update it that way.
But, if you really need to, you can do the following:
- Disable enforcing FK constraints temporarily (e.g.
ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL) - Then update your PK
- Then update your FKs to match the PK change
- Finally enable back enforcing FK constraints
First, we choose stable (not static) data columns to form a Primary Key, precisely because updating Keys in a Relational database (in which the references are by Key) is something we wish to avoid.
For this issue, it doesn't matter if the Key is a Relational Key ("made up from the data"), and thus has Relational Integrity, Power, and Speed, or if the "key" is a Record ID, with none of that Relational Integrity, Power, and Speed. The effect is the same.
I state this because there are many posts by the clueless ones, who suggest that this is the exact reason that Record IDs are somehow better than Relational Keys.
The point is, the Key or Record ID is migrated to wherever a reference is required.
Second, if you have to change the value of the Key or Record ID, well, you have to change it. Here is the OLTP Standard-compliant method. Note that the high-end vendors do not allow "cascade update".
Write a proc. Foo_UpdateCascade_tr @ID, where Foo is the table name
Begin a Transaction
First INSERT-SELECT a new row in the parent table, from the old row, with the new Key or RID value
Second, for all child tables, working top to bottom, INSERT-SELECT the new rows, from the old rows, with the new Key or RID value
Third, DELETE the rows in the child tables that have the old Key or RID value, working bottom to top
Last, DELETE the row in the parent table that has the old Key or RID value
Commit the Transaction
Re the Other Answers
The other answers are incorrect.
Disabling constraints and then enabling them, after UPDATing the required rows (parent plus all children) is not something that a person would do in an online production environment, if they wish to remain employed. That advice is good for single-user databases.
The need to change the value of a Key or RID is not indicative of a design flaw. It is an ordinary need. That is mitigated by choosing stable (not static) Keys. It can be mitigated, but it cannot be eliminated.
A surrogate substituting a natural Key, will not make any difference. In the example you have given, the "key" is a surrogate. And it needs to be updated.
- Please, just surrogate, there is no such thing as a "surrogate key", because each word contradicts the other. Either it is a Key (made up from the data) xor it isn't. A surrogate is not made up from the data, it is explicitly non-data. It has none of the properties of a Key.
There is nothing "tricky" about cascading all the required changes. Refer to the steps given above.
There is nothing that can be prevented re the universe changing. It changes. Deal with it. And since the database is a collection of facts about the universe, when the universe changes, the database will have to change. That is life in the big city, it is not for new players.
People getting married and hedgehogs getting buried are not a problem (despite such examples being used to suggest that it is a problem). Because we do not use Names as Keys. We use small, stable Identifiers, such as are used to Identify the data in the universe.
- Names, descriptions, etc, exist once, in one row. Keys exist wherever they have been migrated. And if the "key" is a RID, then the RID too, exists wherever it has been migrated.
Don't update the PK! is the second-most hilarious thing I have read in a while. Add a new column is the most.
If you are sure that this change is suitable for the environment you're working in: set the FK conditions on the secondary tables to UPDATE CASCADING.
For example, if using SSMS as GUI:
- right click on the key
- select Modify
- Fold out 'INSERT And UPDATE Specific'
- For 'Update Rule', select Cascade.
- Close the dialog and save the key.
When you then update a value in the PK column in your primary table, the FK references in the other tables will be updated to point at the new value, preserving data integrity.
When you find it necessary to update a primary key value as well as all matching foreign keys, then the entire design needs to be fixed.
It is tricky to cascade all the necessary foreign keys changes. It is a best practice to never update the primary key, and if you find it necessary, you should use a Surrogate Primary Key, which is a key not derived from application data. As a result its value is unrelated to the business logic and never needs to change (and should be invisible to the end user). You can then update and display some other column.
for example:
BadUserTable
UserID varchar(20) primary key --user last name
other columns...
when you create many tables that have a FK to UserID, to track everything that the user has worked on, but that user then gets married and wants a ID to match their new last name, you are out of luck.
GoodUserTable
UserID int identity(1,1) primary key
UserLogin varchar(20)
other columns....
you now FK the Surrogate Primary Key to all the other tables, and display UserLogin when necessary, allow them to login using that value, and when they need to change it, you change it in one column of one row only.
Don't update the primary key. It could cause a lot of problems for you keeping your data intact, if you have any other tables referencing it.
Ideally, if you want a unique field that is updateable, create a new field.
You could use this recursive function for generate necessary T-SQL script.
CREATE FUNCTION dbo.Update_Delete_PrimaryKey
(
@TableName NVARCHAR(255),
@ColumnName NVARCHAR(255),
@OldValue NVARCHAR(MAX),
@NewValue NVARCHAR(MAX),
@Del BIT
)
RETURNS NVARCHAR
(
MAX
)
AS
BEGIN
DECLARE @fks TABLE
(
constraint_name NVARCHAR(255),
table_name NVARCHAR(255),
col NVARCHAR(255)
);
DECLARE @Sql NVARCHAR(MAX),
@EnableConstraints NVARCHAR(MAX);
SET @Sql = '';
SET @EnableConstraints = '';
INSERT INTO @fks
(
constraint_name,
table_name,
col
)
SELECT oConstraint.name constraint_name,
oParent.name table_name,
oParentCol.name col
FROM sys.foreign_key_columns sfkc
--INNER JOIN sys.foreign_keys sfk
-- ON sfk.[object_id] = sfkc.constraint_object_id
INNER JOIN sys.sysobjects oConstraint
ON sfkc.constraint_object_id = oConstraint.id
INNER JOIN sys.sysobjects oParent
ON sfkc.parent_object_id = oParent.id
INNER JOIN sys.all_columns oParentCol
ON sfkc.parent_object_id = oParentCol.object_id
AND sfkc.parent_column_id = oParentCol.column_id
INNER JOIN sys.sysobjects oReference
ON sfkc.referenced_object_id = oReference.id
INNER JOIN sys.all_columns oReferenceCol
ON sfkc.referenced_object_id = oReferenceCol.object_id
AND sfkc.referenced_column_id = oReferenceCol.column_id
WHERE oReference.name = @TableName
AND oReferenceCol.name = @ColumnName
--AND (@Del <> 1 OR sfk.delete_referential_action = 0)
--AND (@Del = 1 OR sfk.update_referential_action = 0)
IF EXISTS(
SELECT 1
FROM @fks
)
BEGIN
DECLARE @Constraint NVARCHAR(255),
@Table NVARCHAR(255),
@Col NVARCHAR(255)
DECLARE Table_Cursor CURSOR LOCAL
FOR
SELECT f.constraint_name,
f.table_name,
f.col
FROM @fks AS f
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @Constraint, @Table,@Col
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Del <> 1
BEGIN
SET @Sql = @Sql + 'ALTER TABLE ' + @Table + ' NOCHECK CONSTRAINT ' + @Constraint + CHAR(13) + CHAR(10);
SET @EnableConstraints = @EnableConstraints + 'ALTER TABLE ' + @Table + ' CHECK CONSTRAINT ' + @Constraint
+ CHAR(13) + CHAR(10);
END
SET @Sql = @Sql + dbo.Update_Delete_PrimaryKey(@Table, @Col, @OldValue, @NewValue, @Del);
FETCH NEXT FROM Table_Cursor INTO @Constraint, @Table,@Col
END
CLOSE Table_Cursor DEALLOCATE Table_Cursor
END
DECLARE @DataType NVARCHAR(30);
SELECT @DataType = t.name +
CASE
WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' +
CASE
WHEN c.max_length = -1 THEN 'MAX'
ELSE CONVERT(
VARCHAR(4),
CASE
WHEN t.name IN ('nchar', 'nvarchar') THEN c.max_length / 2
ELSE c.max_length
END
)
END + ')'
WHEN t.name IN ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(4), c.precision) + ','
+ CONVERT(VARCHAR(4), c.Scale) + ')'
ELSE ''
END
FROM sys.columns c
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@TableName)
AND c.name = @ColumnName
IF @Del <> 1
BEGIN
SET @Sql = @Sql + 'UPDATE [' + @TableName + '] SET [' + @ColumnName + '] = CONVERT(' + @DataType + ', ' + ISNULL('N''' + @NewValue + '''', 'NULL')
+ ') WHERE [' + @ColumnName + '] = CONVERT(' + @DataType + ', ' + ISNULL('N''' + @OldValue + '''', 'NULL') +
');' + CHAR(13) + CHAR(10);
SET @Sql = @Sql + @EnableConstraints;
END
ELSE
SET @Sql = @Sql + 'DELETE [' + @TableName + '] WHERE [' + @ColumnName + '] = CONVERT(' + @DataType + ', N''' + @OldValue
+ ''');' + CHAR(13) + CHAR(10);
RETURN @Sql;
END
GO
DECLARE @Result NVARCHAR(MAX);
SET @Result = dbo.Update_Delete_PrimaryKey('@TableName', '@ColumnName', '@OldValue', '@NewValue', 0);/*Update*/
EXEC (@Result)
SET @Result = dbo.Update_Delete_PrimaryKey('@TableName', '@ColumnName', '@OldValue', NULL, 1);/*Delete*/
EXEC (@Result)
GO
DROP FUNCTION Update_Delete_PrimaryKey;
加载中,请稍侯......
精彩评论