Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.

The item can either belong to a parent or child and not both.

I have other tables that are similar to Item and they too can belong to either a Parent or Child.

Should I simply just add 2 nullable FK to them?

Can I enforce that either a Parent or Child must exists using the db?

share|improve this question
    
Is this for mysql or postgres? –  ypercube Apr 29 '13 at 16:11
    
Currently, I'm using postgres. –  Nora Olsen Apr 29 '13 at 16:19
1  
See this answer by @Erwin then: Complex foreign key constraint –  ypercube Apr 29 '13 at 16:40
    
can you describe what you are actually going to store in the database? it may help –  Neil McGuigan Apr 29 '13 at 17:47
    
Can an item belong to more than one child / parent, as long as they are not related ? –  druzin Apr 29 '13 at 18:45
add comment

1 Answer

The solution you outlined is one valid option - assuming that an item can only belong to a single person at any given time.

In PostgreSQL you can enforce mutual exclusion between the two fk columns with a simple CHECK constraint:

either a Parent or Child must exists

... you can add a simple CHECK constraint:

CHECK (a IS NOT NULL OR b IS NOT NULL)

Would demand at least one NOT NULL column - but also allow that both parent_id and child_id exist. If you want to disallow that, too, make it:

CHECK (a IS NOT NULL AND b IS NULL OR b IS NOT NULL AND a IS NULL)
share|improve this answer
    
This solution doesn't really work on MySQL though. Would work fine on SQL server or Oracle. –  druzin Apr 29 '13 at 18:58
    
Sorry, I just saw the MySQL tag on the question and didn't read through that the asker is using Posgres. I Retagged the post –  druzin Apr 29 '13 at 19:00
1  
@druzin: Yeah, this is meant for Postgres, since the OP commented he was using that. –  Erwin Brandstetter Apr 29 '13 at 19:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.