MSSQL - ON DELETE SET NULL

[Philipp Schumann]

Hello NG,

Does SQL Server support "ON DELETE SET NULL"?

I have a couple of CREATE TABLE / ALTER TABLE commands that are all in one
string, separated by ";\r\n", and send them to SQL Server (without
transaction context).

The SQL itself is created dynamically, but here is a sample (for simplicity,
I scrapped irrelevant stuff). I always get an SqlException with as many
SqlErrors in it as there are ON DELETE SET NULL occurances.

The same thing works with Access, though (with Jet-SQL syntax of course). Or
(being a consumer product) is Jet just "fault tolerant" and ignores that?

CREATE TABLE TFoo (
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
Bar UNIQUEIDENTIFIER);

CREATE TABLE TList (
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
);

CREATE TABLE TBar (
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
Foo UNIQUEIDENTIFIER,
List UNIQUEIDENTIFIER,
FOREIGN KEY (Foo) REFERENCES TFoo(ID) ON DELETE SET NULL,
FOREIGN KEY (List) REFERENCES TList(ID) ON DELETE SET NULL);

ALTER TABLE TFoo
ADD FOREIGN KEY (Bar) REFERENCES TBar(ID)
ON DELETE SET NULL;

Many thanks,
Phil

 

[Guest]

Hi Phillip,

AFAIK the closest that you can come to this is setting ON DELETE CASCADE
which will delete the entire row that is associated to the pk. If you need
more specific functionality you can set up a trigger to do what you need and
turn off ON DELETE CASCADE.

I hope this helps.
-----------------------------