Constraints

Hi,

I have the following problem:

I have a table called Jobs with the fields:

JobNumber, Name, Customer, ...

And a table called Customers with the fields:

ID, Name, Address, ...

Obviously Jobs is linked to Customers with the Customer<->ID fields. I want to set it up so that if a Customer is deleted, then any Jobs that had that customer listed now have the Customer field set to NULL. Can I do this with a constraint, or will I need to use a trigger?

Cheers,
Little'un

Hello,
This is a common problem. SQL 2000 and higher have checkboxes in theDiagram Relationship Properties window that allow for cascading updatesand deletes. Behind the scenes, I think this creates a trigger, thoughI'm not sure. Pre SQL 2000, you ha dto write your own trigger.
Note that either triggers and cascading make your database harder todebug. It may not be intuitive to another developer that data issupposed to be deleted in Tables A & B when you delete data inTable A.Developer Emptor.
Jason
|||Note that in SQL Server 2005 there is a new cascading type, SET NULL.
See the following article:
http://www.sqljunkies.com/Tutorial/D839595A-0BC3-4F7E-97EA-DE4911EE2217.scuk
|||

DRI(Declarative Referential integrity) constraint will do it, you don't need code to do it because you can enable it in the table properties when you are creating it. The code below is from the BOL(books online).

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE CASCADE,
qty_ordered int)
GO

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE CASCADE,
qty_ordered int)
GO

Run a search in the BOL(books online) for Cascade Delete. Hope this helps.

|||Caddre,
ON DELETE CASCADE will delete the rows, not set them to NULL as the OP wanted...

|||SET NULL is a new feature in SQL Server 2005. I posted my first look a while back.|||

Unfortunately I don't have SQL Server 2005, so the new feature is no good to me.

I've looked on the Microsoft site and it's not available there, so I'm assuming it's not available yet. Does anyone know a release date?

In the mean time, does anyone know a workaround, I don't really want to have to write it into my ASP code.

Little'un

|||As Jason pointed out, you can write a trigger to handle this.
Something along the lines of:

CREATE TRIGGER tg_delete_setnull
ON YourTable
FOR DELETE
AS
BEGIN
UPDATE SomeOtherTable
SET YourTablePK = NULL
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.YourTablePK = SomeOtherTable.YourTablePK)
END

|||... Actually, that might have to be an INSTEAD OF trigger:

CREATE TRIGGER tg_delete_setnull
ON YourTable
INSTEAD OF DELETE
AS
BEGIN
UPDATE SomeOtherTable
SET YourTablePK = NULL
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.YourTablePK = SomeOtherTable.YourTablePK)
DELETE YourTable
WHERE YourTable.YourTablePK IN
(SELECT YourTablePK
FROM deleted)
END
|||Thank you very much. I can't wait to get my hands on 2005, any ideas when that's out?|||

littlecharva wrote:

Thank you very much. I can't wait to get my hands on 2005, any ideas when that's out?

Later this year