ינו11
Written by:
ronen ariely
11/01/2014 19:04 
Sub Queries is not permitted in Check Constraint. we will show several options to bypass this restriction.
Solution one: Using Function
use QQ
GO
CREATE TABLE Persons(
ID VARCHAR(50) PRIMARY KEY,
FullName VARCHAR(50) NOT NULL
);
CREATE TABLE Class(
ClassNum VARCHAR(30) PRIMARY KEY,
);
-- we can build a function, which use select on table that is not yet exist!
-- but we cant build a table using a function that is not exist,
-- therefor we will build the function first
;CREATE FUNCTION dbo.Fn_NumberOfTeachers(@ClassNum int)
RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = COUNT(*) from Persons_Class S where S.ClassNum = @ClassNum and TeacherOrStudent = 1;
RETURN @ret;
END;
GO
CREATE TABLE Persons_Class(
PersonID VARCHAR(50),
ClassNum VARCHAR(30),
TeacherOrStudent int -- 0 Student / 1 Teacher
constraint Students_PK PRIMARY KEY (PersonID, ClassNum),
constraint Person_FK foreign key (PersonID) references Persons(ID) ON DELETE NO ACTION ON UPDATE CASCADE ,
constraint Class_FK foreign key (ClassNum) references Class(ClassNum) ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT CH1 CHECK (dbo.Fn_NumberOfTeachers(ClassNum) < 2)
);
Let's insert data and check our solution. Execute this part line by line to get the idea of how this is working for us:
insert Persons values (1,'a'),(2,'b') -- studens
insert Persons values (3,'k'),(4,'d') -- teachers
insert Class values (1),(2)
select * from Persons
select * from Class
insert Persons_Class values (1,1,0)
insert Persons_Class values (2,1,0)
insert Persons_Class values (1,2,0)
insert Persons_Class values (2,2,0)
insert Persons_Class values (3,1,1)
insert Persons_Class values (4,1,1) -- ERROR! Our Function check work!
insert Persons_Class values (4,2,1) -- OK
select * from Persons
select * from Class
select * from Persons_Class
* by the way check out this queries to understand about using "ON DELETE NO ACTION ON UPDATE CASCADE", in the above DDL.
update Class set ClassNum = 3 where ClassNum = 1
GO
-- This will update the Persons_Class table as we used "ON UPDATE CASCADE"
delete Class where ClassNum = 2
GO
-- This will return ERROR as we used " ON DELETE NO ACTION"
-- therefor we can not delete a record which have REFERENCE from the Persons_Class table
Let's clean the DDL
DROP TABLE Persons
DROP TABLE Class
DROP TABLE Persons_Class
Option two: using trigger.
we can replace the use of CHECK CONSTRAINT with a simple TRIGGER on Insert + a TRIGGER on update.
.