1

I am aware that check constraints are automatically inherited by the parent table's children(s) in PostgreSQL, however my concern is with Oracle.

When you implement a CHECK constraint for one of the columns in the parent table, do you also implement them for that same column (the FK) of the child table(s)?

Improve this question

1 Answer 1

Reset to default
4

No, if the child is using a FK (foreign key) on that column, there is no reason to use a separate CHECK since the FK constrains it to the parent's values, which is using the CHECK already.

In fact, I'd recommend against implementing the CHECK in child because that is repeating logic. Try not to repeat integrity rules; in case you need to change them, you want to change them in one place. It is the parent that specifies what values are legal (typically) in this type of relationship.

CAVEAT: It may be needful to include the check constraints in child as well, in case of materialized views and query rewrite option. Though I have no proof, it is something to investigate. In addition, see @JeffreyKemp comments for possible optimizer impact, though I have assumed the stats analyzer would have enough information from the foreign key and parent table.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I have a similar question. Does the same apply to CASCADE CONSTRAINTS? That is, do you apply an "ON CASCADE DELETE/UPDATE" on the child table or the parent table? I have seen it being applied to the former, however I thought it would have been more appropriate to see it in applied on the latter (parent table). May I ask for your comments (furthermore, from your assertion, I would also assume that you would also advise implementing it in both?)?
The CASCADE DELETE clause is an option for the foreign key constraint, so for any parent-child relationship there is only the foreign key from child to parent on which you enable CASCADE (or not). If you've seen it on both parent and child, that likely means there was another key in the mix and you were confusing them. Maybe the parent also had a FK to another table. There is only one place to configure the CASCADE DELETE option in this case.
There may be one reasonable argument for some cases where a check constraint has benefit being repeated on the child table: query optimisation. The CBO as far as I can see does not (at least currently as of 11gR2) derive check constraints from parent tables when optimising queries on a child table. Of course, this is certainly not a reason to copy ALL check constraints to child tables. Also, in these cases a better optimisation may be to join to the parent table; this then brings the parent's check constraint into play for the optimiser.
@JeffreyKemp - Actually your comment reminds me that there may be another case, with materialized views and query rewrite. I may amend my answer with caveats. I've never considered the case you suggested, always assuming the optimizer could work off the stats collection, and the foreign key, but I can accept that it may not be that cut and dried. If you've got an example of difference I'll paste it in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.