ינו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.
.