Weird behavior with a foreign key and "on delete set null"
-
So I have two tables
CREATE TABLE dbo.Orders
(
order_id int NOT NULL IDENTITY(1,1) CONSTRAINT PK_ORDERS PRIMARY KEY CLUSTERED,
order_description varchar(250) NOT NULL
);
CREATE TABLE dbo.Invoices
(
invoice int NOT NULL CONSTRAINT PK_INVOICES PRIMARY KEY CLUSTERED,
sales_amount decimal(15,4) NOT NULL CONSTRAINT DF_INVOICES_salesamount DEFAULT (0.00),
order_id int NULL CONSTRAINT FK_INVOICES_ORDERS FOREIGN KEY REFERENCES dbo.Orders(order_id) ON DELETE SET NULL,
deleted bit NOT NULL CONSTRAINT DF_INVOICES_deleted DEFAULT (1)
);
Each table also has a couple of after triggers for insert and update, the invoices table has an instead of delete trigger, and there's an indexed view that references both tables.
The problem is that the "ON DELETE SET NULL" doesn't seem to work. If I test it, it behaves as though "No action" is the behavior, and if I do an sp_help, the results there also suggest "No action" but if I look in sys.foreign_keys, the delete_referential_action is set to SET_NULL.
I'm not sure what's causing the behavior or if the above is enough to go by. So far, I haven't had much luck looking through BOL; was hoping someone might have some insight as to where I could confirm why the behavior is happening.
-
it looks like sp_help does nto correctly identify the delete actions.
if i run this i see the action correctly:
select * from sys.foreign_keys
there's a connect item complaining about it here:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
just to confirm, it shows correctly in SQL2012.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Ah good, gives another small bit of ammunition to maybe upgrade to 2012. 🙂
The actual behavior isn't matching the ON SET NULL either, unfortunately. I looked a little more closely at the triggers, and both are running after insert/update/delete triggers that update Invoices.order_id if there are any changes to either table (The Invoices table just has update/insert, the delete is an instead of that sets the deleted bit).
I think I'm going to talk to the original developer and see if I can disable/remove those triggers and just use the cascading options on the FK instead; it seems like that should be sufficient.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply