CODEWITHSUNDEEP
sqlpostgresqlconstraints
xcoder
xcoder

Reputation: 1446

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

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?

Upvotes: 0

Views: 94

Answers (2)

Techie
Techie

Reputation: 1491

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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.

Upvotes: 2

Related Questions