I have a situation where I need to use a DELETE SET NULL constraint on a foreign key. I have implemented it and it works fine.
My problem is, I need some DDL T-SQL that looks at the current foreign key in the database to determine if it is using the DELETE SET NULL attribute. If not, I want to drop the foreign key and re-create it with the attribute.
Something like this:
DECLARE @SqlString varchar(1000)
@SqlString varchar(1000)DECLARE @ConstraintName varchar(100), @isDelete bit, @isCascadeDelete bitSELECT @ConstraintName = object_name(constid), @isCascadeDelete = OBJECTPROPERTY(constid, N'CnstIsDeleteCascade')
@ConstraintName varchar(100), @isDelete bit, @isCascadeDelete bitSELECT @ConstraintName = object_name(constid), @isCascadeDelete = OBJECTPROPERTY(constid, N'CnstIsDeleteCascade')
@ConstraintName = object_name(constid), @isCascadeDelete = OBJECTPROPERTY(constid, N'CnstIsDeleteCascade')FROM sysreferences INNER JOIN sysobjects f ON sysreferences.fkeyid = f.id
sysreferences INNER JOIN sysobjects f ON sysreferences.fkeyid = f.idINNER JOIN sysobjects r ON sysreferences.rkeyid = r.id
JOIN sysobjects r ON sysreferences.rkeyid = r.idWHERE f.name = 'ScheduledServices' AND r.name = 'Transactions'
f.name = 'ScheduledServices' AND r.name = 'Transactions'IF @ConstraintName IS NOT NULL AND @isCascadeDelete = 0
@ConstraintName IS NOT NULL AND @isCascadeDelete = 0BEGIN
SET @SqlString = 'ALTER TABLE ScheduledServices DROP CONSTRAINT ' + @ConstraintNameEXECUTE (@SqlString)
SET @SqlString = 'ALTER TABLE ScheduledServices DROP CONSTRAINT ' + @ConstraintNameEXECUTE (@SqlString)
EXECUTE (@SqlString)SET @ConstraintName = NULL
SET @ConstraintName = NULLEND
-- Check to see if constraint with cascade set null exists. If not, add it.
IF @ConstraintName IS NULL
@ConstraintName IS NULLBEGIN
EXECUTE ('ALTER TABLE ScheduledServices ADD CONSTRAINT FK_ScheduledServices_TransactionId'
EXECUTE ('ALTER TABLE ScheduledServices ADD CONSTRAINT FK_ScheduledServices_TransactionId'+ ' FOREIGN KEY (TransactionId) REFERENCES Transactions (TransactionId) ON DELETE SET NULL')
+ ' FOREIGN KEY (TransactionId) REFERENCES Transactions (TransactionId) ON DELETE SET NULL')END
My problem is that the value CnstIsDeleteCascade is not the objectproperty I want. I wan the one that designates the constraint as a "delete set null" constraint. Google and MSDN searches have not turned up what the magic value is. Or perhaps there is another way?
Any help would be appreciated!
Ken