Problem with subqueries in CHECK constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with subqueries in CHECK constraint

Author

Topic

ILF
Starting Member

3 Posts

Posted - 2009-12-09 : 06:53:20

Hello!

I currently study a basic course in databases and have been given an assignment to make a java program that communicates with an SQL Server. It is thought to be a system where you can register students and courses and also type in who studies what courses.

Right now i have however run into a problem i can not seem to solve, I want to create a check constraint that makes sure a student can not enroll in courses corresponding to a total of more than 45 credits. Here is basically what I have tried: (apparently, however, SQL Server wont let me use subqueries in my check)

(ccode = CourseCode, snr = StudentNr, historik = either or not they are actively studying it or if they have done it in the past(1 = currently studying), I want to collect the SUM of all courses currently being studied + the value of the new one he or she wants to add and see to it that this value does not exceed 45)

CONSTRAINT
CREDITS_CHK CHECK(
(SELECT SUM(credits) FROM Course c where c.ccode IN(
SELECT s.ccode FROM Studies s where(s.snr = snr and s.historik = 1) or c.ccode = ccode)) <= 45)

Any help is greatly appreciated!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 07:59:15

To use a check constraint which references more than the current row, you need to use a function, as in this example [url]http://msdn.microsoft.com/en-us/library/ms188258.aspx[/url]

Alternatively, you may want to consider using a trigger.

Post your table structure and some sample data if you'd like more specific help.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

ILF
Starting Member

3 Posts

Posted - 2009-12-09 : 08:31:35

Hello again!

Thank you for your speedy reply.

I am posting the entire query here: (note: if some of the variable names sound weird its because I have translated them from Swedish into English)

CREATE TABLE Student
(spnr VARCHAR(6) PRIMARY KEY,
sname VARCHAR(10),
sadress VARCHAR(10),
stel VARCHAR(10));

CREATE TABLE Course
(ccode VARCHAR(5) PRIMARY KEY,
kname VARCHAR(10),
kadress VARCHAR(10),
credits integer);

CREATE TABLE Studies
(spnr VARCHAR(5) NOT NULL,
ccode VARCHAR(5) NOT NULL,
grade integer,
historik integer,

CONSTRAINT
LASER_PK PRIMARY KEY(spnr,ccode),

CONSTRAINT
LASERSPNR_FK FOREIGN KEY(spnr)
REFERENCES Student(spnr)
ON DELETE CASCADE,

CONSTRAINT
LASERKKOD_FK FOREIGN KEY(ccode)
REFERENCES Course(ccode)
ON DELETE CASCADE,

CONSTRAINT
CREDITS_CHK CHECK(
(SELECT SUM(credits) FROM Course c where c.ccode IN(
SELECT s.ccode FROM Studies s where(s.spnr = spnr and s.historik = 1) or c.ccode = ccode)) <= 45)

insert into Student values('890101','anna','lund','046111') ;
insert into Student values('890202','anna','malmö','040111') ;
insert into Student values('890303','eva','lund','046222');
insert into Student values('890404','eva','malmö','040222');
insert into Student values('890505','hans','eslöv','042111');

insert into Course values('K1','dat001','lund',10) ;
insert into Course values('K2','dat002','lund',5) ;
insert into Course values('K3','dat003','malmö',20);
insert into Course values('K4','inf001','lund',10);
insert into Course values('K5','inf002','lund',5);
insert into Course values('K6','inf003','malmö',20);
insert into Course values('K7','inf004','dalby',20);

insert into Studies values('890101','K1',6, 0);
insert into Studies values('890101','K2',7, 0);
insert into Studies values('890101','K3',8, 0);
insert into Studies values('890101','K4',6, 0);
insert into Studies values('890202','K1',7, 0);
insert into Studies values('890202','K2',9, 0);
insert into Studies values('890202','K3',7, 0);
insert into Studies values('890303','K1',6, 0);

This is the entire query.

I'm really grateful for the help.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 10:03:34

Here's some stuff to play with...

drop table Studies
drop table Student
drop table Course
drop function MyCheckFunction

CREATE TABLE Student
(spnr VARCHAR(6) PRIMARY KEY,
sname VARCHAR(10),
sadress VARCHAR(10),
stel VARCHAR(10));

CREATE TABLE Course
(ccode VARCHAR(5) PRIMARY KEY,
kname VARCHAR(10),
kadress VARCHAR(10),
credits integer);

CREATE TABLE Studies
(spnr VARCHAR(6) NOT NULL, --changed 5 to 6
ccode VARCHAR(5) NOT NULL,
grade integer,
historik integer,

CONSTRAINT
LASER_PK PRIMARY KEY(spnr,ccode),

CONSTRAINT
LASERSPNR_FK FOREIGN KEY(spnr)
REFERENCES Student(spnr)
ON DELETE CASCADE,

CONSTRAINT
LASERKKOD_FK FOREIGN KEY(ccode)
REFERENCES Course(ccode)
ON DELETE CASCADE,
)
go


CREATE FUNCTION MyCheckFunction()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = (SELECT SUM(credits) FROM Course c where c.ccode IN(
SELECT s.ccode FROM Studies s where(s.spnr = spnr and s.historik = 1) or c.ccode = ccode))
   RETURN @retval
END;
go

ALTER TABLE Studies ADD CONSTRAINT chkCredits CHECK (dbo.MyCheckFunction() <= 45); --comment this to see the trigger in action
go

create trigger tr_Studies_InsertOrUpdate on Studies for insert, update as
if (SELECT SUM(credits) FROM Course c where c.ccode IN(
SELECT s.ccode FROM Studies s where(s.spnr = spnr and s.historik = 1) or c.ccode = ccode)) > 45
begin
    raiserror('Insufficient credit', 16, 1)
    rollback tran
end
go

insert into Student values('890101','anna','lund','046111') ;
insert into Student values('890202','anna','malmö','040111') ;
insert into Student values('890303','eva','lund','046222');
insert into Student values('890404','eva','malmö','040222');
insert into Student values('890505','hans','eslöv','042111');

insert into Course values('K1','dat001','lund',10) ;
insert into Course values('K2','dat002','lund',5) ;
insert into Course values('K3','dat003','malmö',20);
insert into Course values('K4','inf001','lund',10);
insert into Course values('K5','inf002','lund',5);
insert into Course values('K6','inf003','malmö',20);
insert into Course values('K7','inf004','dalby',20);

insert into Studies values('890101','K1',6, 0);
insert into Studies values('890101','K2',7, 0);
insert into Studies values('890101','K3',8, 0);
insert into Studies values('890101','K4',6, 0);
insert into Studies values('890202','K1',7, 0);
insert into Studies values('890202','K2',9, 0);
insert into Studies values('890202','K3',7, 0);
insert into Studies values('890303','K1',6, 0);

insert into Studies values('890101','K5',6, 0); --should be prevented from going in
go

select * from Student
select * from Course
select * from Studies

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

ILF
Starting Member

3 Posts

Posted - 2009-12-10 : 15:58:22

Hello once again!

I have played around a bit with your examples now and I think that I have gotten the hang of it.

I just wanted to say thank you for your help!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-11 : 05:43:45

Excellent! Thanks for the feedback

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources