6 replies to this topic

[rubbadrools]

Hi!

I got a problem with some code and I need you expertise ^^
I am making a database for the classic student,course,studies scenario.
I need to make this check constraint wich doesnt allow a student to study more than 45ECTS at a time

Here is what I got so far... :

ALTER function [dbo].[check_stud](@stId int, @ccode int)

returns int

as

begin

declare @coursepoints int;

declare @studiespoints int;

declare @return int;



select @coursepoints = c.points

from course c

where c.code = @ccode


select @studiespoints = sum(s.points)

from studies s

where s.id = @stId



select @return = @coursepoints + @studiespoints;


return @return

end

And We also have the Check Constraint u put in after:

alter table studies add constraint pointscheck check ((dbo.check_stud(id,code)<(46)))

then we have the create tables just in case u need em' :

create table course(

code int,

cname varchar(25),

points int,

primary key(code));


create table prereq(

code int,

prereqcode int,

primary key(code, prereqcode),

foreign key(code) references course on delete cascade,

foreign key(prereqcode) references course(code));


create table student(

id int,

stype varchar(25) not null,

primary key(id), 

check (stype in('swedishstudent', 'foreignstudent')));


create table swedishstudent(

id int,

pnr int not null,

name varchar(30),

primary key(id),

unique(pnr),

foreign key(id) references student on delete cascade);


create table foreignstudent(

id int,

name varchar (30),

country varchar (20),

primary key(id),

foreign key(id) references student on delete cascade);


create table studies(

id int,

code int,

points int,

primary key(id,code),

foreign key(id) references student on delete cascade,

foreign key(code) references course on delete cascade);


create table hasstudied(

id int,

code int,

grade varchar(2),

primary key(id,code),

foreign key(id) references student on delete cascade,

foreign key(code) references course on delete cascade,

check (grade in('U', 'G', 'VG')));


create table section(

code int,

sname varchar(30),

primary key(code,sname),

foreign key(code) references course on delete cascade);


create table assignment(

code int,

sname varchar(30),

aname varchar(30),

primary key(code,sname,aname),

foreign key(code,sname) references section on delete cascade);

Just can't put my finger on whats wrong... plz Help ^^

[WingedPanther]

Where are you getting an error? Is the constraint not working, can't be added, other?

[rubbadrools]

I can insert into the studies table, but my constraint kicks in what to me seems like in an irregular manner. for example a student can study 30ects but when i try to add 10 more it goes to the constraint even though its below 45. I dont think i understand what my function really does :/

[WingedPanther]

It occurs to me it may be counting the 10 twice. What happens if you insert 30, then 8? What happens if you insert 30, then 7?

[rubbadrools]

I can't insert 30 at once, I can insert 15, 15 then 10 wont work and neither will 8.... I think it may have something to do with the number of courses inserted... and furthermore I can't insert a course with 30 points.... Getting more and more lost cuz i can't pinpoint the location of my errors...

[rubbadrools]

Solved it!! :)
I Removed the select + @studiespoints;
from select @return = @coursepoints + @studiespoints;
then joined c.coursecode with s.coursecode...
also dropped the points column in studies :)

Ty for ut efforts! :) Much appreciated!

[WingedPanther]

Glad to hear it :) It felt like a double count happening.