Sql Query error in check statement

Question

I've to Define a table constraint on Dept that will ensure that all managers have age > 30. I'm a beginner in it so I do not want to use user defined function. Here I want a simple solution. which can apply my check in create table statement. I want to use check statement or assertion statement.

create table emp(eid int primary key,ename varchar(20),age int,salary real CHECK (salary >= 10000));
create table dept(did int primary key, buget real,managerid int foreign key (managerid) references emp check ((select eid from emp where age >30)));

and its giving the following error i do not know why

Msg 156, Level 15, State 1, Line 54
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ')'.

I'm using Microsoft sql management studio 2014.

Tell me where I'm doing wrong. Thanks!

As an aside, you shouldn't store the `age` as a fixed value, since you'll need to update it every time the age changes. Store the date of birth instead and have the `age` be a computed column — Lamak, Mar 23 '17 at 13:28
Give a look: http://stackoverflow.com/questions/3880698/can-a-check-constraint-relate-to-another-table or http://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint — etsa, Mar 23 '17 at 13:29
What you're describing is a business rule. Not a data integrity rule. As such, it's better to implement that rule in the application layer rather than the database layer. — Disillusioned, Mar 23 '17 at 13:48

score 1 · Accepted Answer · answered Mar 23 '17 at 13:38

You can do this without a UDF. You can use a foreign key constraint and computed columns.

create table emp (
    eid int primary key,
    ename varchar(20),
    age int,
    salary real CHECK (salary >= 10000),
    manager_eligible as (case when age > 30 then 1 else 0 end),
    unique (manager_eligible, eid)
);

create table dept(
    did int primary key,
    buget real,
    managerid int,
    manager_eligible as (1),
    foreign key (manager_eligible, managerid) references emp(manager_eligible, eid)
);

I should add that UDFs usually slow things down. So being able to do this with foreign keys should also make inserts and updates faster.

That is an interesting way of using a computed column with a foreign key. Pretty slick. — Sean Lange, Mar 23 '17 at 13:59

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

You can create a User Defined Function. Give a look at Give a look: Can a Check constraint relate to another table? or Sub queries in check constraint

edited May 23 '17 at 12:17

Community

1
1

answered Mar 23 '17 at 13:32

etsa

5,020
1
7
18

Sql Query error in check statement

2 Answers2