Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Weird behavior with a foreign key and "on delete set null" Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 12:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 246, Visits: 216
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.
Post #1361565
Posted Wednesday, September 19, 2012 1:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,749, Visits: 31,114
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1361597
Posted Wednesday, September 19, 2012 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,749, Visits: 31,114
just to confirm, it shows correctly in SQL2012.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1361599
Posted Wednesday, September 19, 2012 1:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 246, Visits: 216
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.
Post #1361607
Posted Thursday, September 20, 2012 5:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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.


Not only that, it will be more effective. Triggers are procedural code (ugh!); the optimizer cannot use them. DRI actions can be.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1361877
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse