For tables like this:
Book
| id | type +----+-------- | 1 | audio | 2 | paper PK id SEQUENCE
AudioBook
| book_id | type +---------+----------- | 1 | audio FK book_id references Book FK constraint book_id + type references Book
PaperBook
| book_id | type +---------+-------- | 2 | paper FK book_id references Book FK constraint book_id + type references Book
Is there other way to ensure that PaperBook and AudioBook will not have same book_id without type column on them (remove FK constraint book_id + type references Book and type on childs?
Example without fk constraint where AudioBook and PaperBook can have book_id=1 at the same time:
Book
| id | type +----+-------- | 1 | audio | 2 | paper PK id SEQUENCE
AudioBook
| book_id +--------- | 1 FK book_id references Book
PaperBook
| book_id +--------- | 2 FK book_id references Book
Advertisement
Answer
approach one: foreign key constraints ensure data integrity between the parent table and children tables. a foreign key constraint prevents the child table from inputting a value for a key not found in the parent table.
without type, you will want to use a guid to keep the key unique on the foreign constraint. I don’t like guids because you lose readibility.
approach two: in general a foreign key id is a unique number referenced in the parent table. if you are sharing between multiple tables and a single parent id than consider creating a many to many table to join the tables using key id and type, a cross reference table for lookup.
you can populate and remove items from the cross reference table using a trigger