Вот моя проблема: У меня есть 2 таблицы:
|ID|OTHER_STAF|, где ID является первичным ключом|FPK|ID|SOMETHING_ELSE|, где комбинация FPK и ID делает первичный ключ, а также ID - это внешний ключ, на который ссылается WORKER.ID(не имеет значения null и имеет то же значение, что и в WORKER).Я хочу сделать хранимую процедуру UPDATE_ID_WORKER, где я хотел бы изменить значение определенного ID в WORKER, а также во всех экземплярах определенного значения ID в FIRM.
хранимая процедура:
........ @Я бы .. ???? ........
Вы не должны этого делать, а вместо этого вставлять в новую запись и обновлять ее таким образом.
Но, если вам действительно нужно, вы можете сделать следующее:
ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)Сначала мы выбираем стабильные (не статические) столбцы данных для формирования Первичного ключа, именно потому, что обновление ключей в реляционной базе данных (в которой ссылки являются ключами) - это то, чего мы хотим избежать.
Для этой проблемы не имеет значения, является ли ключ реляционным ключом ( "составлен из данных" ) и, следовательно, имеет реляционную целостность, мощность и скорость, или если "ключ" идентификатор записи, при этом ни одна из этих реляционных целостностей, мощности и скорости. Эффект тот же.
Я заявляю об этом, потому что есть много сообщений от невежественных, которые предполагают, что это точная причина, по которой записи ID как-то лучше, чем Relational Keys.
Дело в том, что идентификатор ключа или записи переносится туда, где требуется ссылка.
Во-вторых, если вам нужно изменить значение ключа или идентификатора записи, вы должны его изменить. Вот стандарт OLTP, соответствующий стандарту. Обратите внимание, что высокопроизводительные поставщики не разрешают "каскадное обновление".
Напишите proc. Foo_UpdateCascade_tr @ID, где Foo - имя таблицы
Начать транзакцию
Сначала INSERT-SELECT введите новую строку в родительской таблице из старой строки с новым значением Key или RID
Во-вторых, для всех дочерних таблиц, работающих сверху вниз, INSERT-SELECT новые строки из старых строк с новым значением Key или RID
В-третьих, УДАЛИТЕ строки в дочерних таблицах, которые имеют старое значение Key или RID, работая снизу вверх
Last, DELETE строка в родительской таблице, которая имеет старое значение Key или RID
Зафиксировать транзакцию
Другие ответы неверны.
Отключение ограничений, а затем их включение после UPDATing требуемых строк (родитель плюс все дети) - это не то, что человек будет делать в онлайн-производственной среде, если они хотят оставаться занятыми. Этот совет хорош для однопользовательских баз данных.
Необходимость изменения значения ключа или МПОГ не является показателем дефекта дизайна. Это обычная потребность. Это смягчается путем выбора стабильных (не статических) ключей. Его можно смягчить, но его нельзя устранить.
Суррогат, заменяющий естественный Ключ, не имеет никакого значения. В примере, который вы указали, "ключ" является суррогатом. И его необходимо обновить.
Нет ничего сложного в каскадировании всех необходимых изменений. См. Шаги, приведенные выше.
Нет ничего, что можно было бы предотвратить, если вселенная изменится. Меняется. Смирись с этим. И поскольку база данных представляет собой совокупность фактов о юниверсе, когда вселенная изменяется, база данных должна измениться. Это жизнь в большом городе, это не для новых игроков.
Люди, выйдя замуж и собираемые ежи, не являются проблемой (несмотря на то, что такие примеры используются, чтобы предположить, что это проблема). Потому что мы не используем имена как ключи. Мы используем небольшие, стабильные идентификаторы, которые используются для идентификации данных во Вселенной.
Не обновляйте ПК! это вторая по важности забавная вещь, которую я прочитал в то время. Добавить новый столбец больше всего.
Если вы уверены, что это изменение подходит для среды, в которой вы работаете: установите условия FK на вторичных таблицах для ОБНОВЛЕНИЯ CASCADING.
Например, если SSMS используется как GUI:
Когда вы обновляете значение в столбце PK в своей основной таблице, ссылки FK в других таблицах будут обновлены, чтобы указать на новое значение, сохраняя целостность данных.
Если вам необходимо обновить значение первичного ключа, а также все соответствующие внешние ключи, тогда необходимо исправить весь проект.
Сложно каскадировать все необходимые изменения внешних ключей. Лучше всего никогда не обновлять первичный ключ, и если вы сочтете это необходимым, вы должны использовать Surrogate Primary Key, который является ключом, не полученным из данных приложения. В результате его значение не связано с бизнес-логикой и никогда не нуждается в изменении (и должно быть невидимым для конечного пользователя). Затем вы можете обновить и отобразить другой столбец.
например:
BadUserTable
UserID varchar(20) primary key --user last name
other columns...
когда вы создаете много таблиц с FK для UserID, чтобы отслеживать все, над чем работал пользователь, но затем пользователь выходит замуж и хочет, чтобы ID соответствовал их новой фамилии, вам не повезло.
GoodUserTable
UserID int identity(1,1) primary key
UserLogin varchar(20)
other columns....
теперь вы FK суррогатный первичный ключ ко всем остальным таблицам и, при необходимости, отображаете UserLogin, позволяете им входить в систему с использованием этого значения, а когда им нужно его изменить, вы меняете его только в одном столбце одной строки.
Не обновлять первичный ключ. Это может вызвать множество проблем, связанных с сохранением ваших данных, если у вас есть другие таблицы, ссылающиеся на него.
В идеале, если вы хотите, чтобы новое поле было обновляемым, создайте новое поле.
Вы можете использовать эту рекурсивную функцию для генерации необходимого 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;