SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird behavior with a foreign key and "on delete set null"


Weird behavior with a foreign key and "on delete set null"

Author
Message
Jason Whitish
Jason Whitish
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 281
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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:
http://connect.microsoft.com/SQLServer/feedback/details/616475/sp-help-doesnt-show-set-null-in-foreign-keys

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!

Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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!

Jason Whitish
Jason Whitish
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 281
Ah good, gives another small bit of ammunition to maybe upgrade to 2012. Smile

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search