2

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

BastanteCaro
  • 1,269
  • 1
  • 13
  • 24
  • Have you only just set this up? If so did you press save in SSMS? It seems the FK constraint must be disabled or not present at all if it leaves you with an orphaned row. Otherwise can you script out full DDL for both tables involved? – Martin Smith Jan 25 '11 at 15:33
  • Yeah. I've saved and also use VStudio and the schema has passed over to the Designer. but still its not working. whether i do a delete in SSMS or programatically through VStudio. – BastanteCaro Jan 25 '11 at 15:43
  • Can you script out the DDL for both tables (including the FK definition) and add it to your question? – Martin Smith Jan 25 '11 at 15:45
  • Thanks. Tables now added to the Post – BastanteCaro Jan 25 '11 at 15:52
  • 1
    None of the above links (screen shot or the two code snippets) work any more. – Richard Fawcett Nov 23 '13 at 14:20

1 Answers1

3

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'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thanks that was great. it seems to work perfect now. i have no idea what was the problem previously. i didn't change anything, but thanks for the help. – BastanteCaro Jan 27 '11 at 02:37