CODEWITHSUNDEEP
mysqlsql
aaa
aaa

Reputation: 57

SQL Select query inside a check constraint

So i have these tables

student(num int, age int); 

ex(id int, num int);

i want to add a constraint when inserting informations in ex to check if the student age is bigger than 20 (No triggers or functions)

This is my attempt

ALTER TABLE ex
  ADD CONSTRAINT x_check CHECK((SELECT age FROM student S WHERE num=S.num )>20);

But it doesn't work, any solution ?

Upvotes: 0

Views: 5657

Answers (2)

Walker Farrow
Walker Farrow

Reputation: 3885

The answer is that a CHECK is a table-level constraint. You cannot (at least to my knowledge) add a constraint (outside a FK constrain) that runs a select statement against a DIFFERENT table.

The reason for this is simple. If the OTHER table changes (student), then it would NOT enforce integrity on the "ex" table.

So you could do several things, depending on what suits best:

a) Whatever script does the inserts/updates to check if the age is >=20 BEFORE inserting, else it should error

b) Set up a trigger that runs BEFORE insert or update to check this, else fails. But the trigger would have to be set up on BOTH tables in order to maintain table integrity.

Hope this helps

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270081

It is irrelevant anyway because MySQL does not enforce check constraints. However, you can do something with a foreign key constraint. You want to ensure that students in ex are over 20.

Ideally, you could just change the student table to have a column like:

alter table student add StudentOver20 as (case when age > 20 then num end);

alter table student add key (StudentOver20);

Then, a foreign key to this column would enforce the constraint:

alter table ex add foreign key (num) references student(StudentOver20)

Unfortunately, in MySQL, you cannot add the column with a value like that. Instead, you need to update the value:

alter table student add StudentOver20 int;

update student set StudentOver20 = (case when age > 20 then num end);

If you want to keep it up-to-date, then you need to add a trigger. Of course, if you are adding a trigger, you might as well add it to ex to do the check in the trigger.

Do note that storing age in a table is a bad idea. The values will definitely be incorrect one year from now.

Upvotes: 0

Related Questions