Adding constraint to a table referencing a subset of records of another table

Question

Say I have the following tables:

User(user_id, name, age,...)
Job(job_id, salary, user_id)

I want to have a constraint on Job so that whenever the job's salary is > 20,000, the user doing the job must be of age >= 18. I have tried the CHECK constraint but it seems to work on single table only?

You need to use a trigger defined to fire before or instead of insert, and use that to do the check and allow/disallow the insert. — jpw, Jan 16 '16 at 19:34
Check [this thread](http://stackoverflow.com/questions/10135754/how-to-make-a-foreign-key-with-a-constraint-on-the-referenced-table-in-postgresq?rq=1) for using user defined function with check constraint, this might help in your case — Techie, Jan 16 '16 at 19:39

score 2 · Answer 1 · edited Jan 16 '16 at 23:10

2

That's correct, check constraints work on single table only. You've to use Trigger procedure to block the unwanted data, refer the below link on Trigger Procedures.

Postgresql Documentation - Trigger Procedures

edited Jan 16 '16 at 23:10

answered Jan 16 '16 at 19:36

Techie

1,491
3
18
24

score 2 · Accepted Answer · answered Jan 16 '16 at 19:45

You can do this with a user-defined function. The function can look up the value in the other table.

I'm not necessarily recommending this approach. For many applications, I wrap insert/update statements in a stored procedure/function and put the logic there. However, this is possible without a trigger.

Adding constraint to a table referencing a subset of records of another table

2 Answers2