en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
ינו11

Written by: ronen ariely
11/01/2014 19:04 RssIcon

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.

.