CREATE ASSERTION with Microsoft SQL Server

[Morten Gulbrandsen]

Hi programmers,

Please,

how can this be coded in Microsoft SQL Server


CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT 1,

CONSTRAINT EMPPK
PRIMARY KEY (SSN),

CONSTRAINT EMPSUPERFK
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL ON UPDATE CASCADE,

CONSTRAINT EMPDEPTFK
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,

CONSTRAINT DEPTPK
PRIMARY KEY (DNUMBER),
CONSTRAINT DEPTSK
UNIQUE (DNAME),
CONSTRAINT DEPTMGRFK
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);


CREATE ASSERTION SALARY_CONSTRAINT
CHECK (
NOT EXISTS
(
SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.SALARY > M.SALARY
AND
E.DNO=D.DNUMBER
AND
D.MGRSSN=M.SSN
)
);


I found no documentation of the CREATE ASSERTION statement here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp


Does it exist another statement ?

Yours Sincerely

Morten Gulbrandsen

[Anith Sen]

There is no CREATE ASSERTION DDL in SQL Server. One way you can implement
your requirement is to write a trigger which includes the query used in your
CHECK constraint like:

CREATE TRIGGER trg ON tbl FOR INSERT, UPDATE
AS
...
IF EXISTS( SELECT * FROM ...)
ROLLBACK

Alternatively you can include this in your INSTEAD OF trigger as well,
however, such triggers (both before & after) can be cumbersome and
performance dampening.

[WangKhar]

is that not simply a Check Constraint?

[Gert-Jan Strik]

No. A trigger such as

CREATE TRIGGER trg ON tbl FOR INSERT, UPDATE
AS
...
IF EXISTS( SELECT * FROM ...)
ROLLBACK

can access other rows and tables, something a CHECK constraint cannot. A
CHECK constraint can only reference the columns of the row in question.

An ASSERTION is basically a CHECK constraint at the database level and
can reference all columns of all rows of all tables in the database.
Unfortunately, SQL-Server does not support this. And I haven't seen any
anouncement that it will be supported in the next version Yukon.

On the other hand, one might debate whether the business logic the OP
described should be hard coded in the database...

Gert-Jan

Post by WangKhar
is that not simply a Check Constraint?