CODEWITHSUNDEEP
nullconstraintssql-server-expressforeign-key-relationshipcascading-deletes
BastanteCaro
BastanteCaro

Reputation: 1290

SQL Server Foreign Key "On Delete Set Null " constraint not working

I am using sql server express 2008 with mmse. i have set up a foreign key constraint between to tables and set the on delete constraint to "set null". however it does no seem to enforce the constraint and i am left with the ID in the field of the now deleted row.

here is a small screen shot of how i have it set up

where could i be going wrong?

Table 1 Table 2

Upvotes: 2

Views: 4069

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

The DDL you posted works fine for me.

declare @id int;
INSERT INTO DeliveryAreas(Description,Rate) VALUES ('To Delete', 100)
set @id=SCOPE_IDENTITY()
INSERT INTO Customer(FName,DeliveryAreaID) VALUES ('Test',@id)
SELECT ID,FName,DeliveryAreaID FROM Customer
DELETE FROM DeliveryAreas WHERE ID=@id
SELECT ID,FName,DeliveryAreaID FROM Customer

Returns

ID          FName                          DeliveryAreaID
----------- ------------------------------ --------------
1           Test                           3


ID          FName                          DeliveryAreaID
----------- ------------------------------ --------------
1           Test                           NULL

Can you just double check the enabled status your end?

SELECT is_disabled,* FROM sys.foreign_keys where name='FK_Customer_DeliveryAreas'

Upvotes: 3

Related Questions