CODEWITHSUNDEEP
oracle-databaseconstraintsreferential
samg
samg

Reputation: 333

Check constraint to enforce referential integrity?

Can we use a check constraint to enforce referential constraint? Let's say I have a column that contains a UUID; this UUID can reference either table A or B depending on the value of a 2nd column.

------------------------------------------
|ID      | Type      | PK in Other Table |
------------------------------------------
|1       | Employee  |    500            |
------------------------------------------
|2       | Store     |    7000           |
------------------------------------------

so record #1 points to a record in the employee table, #2 points to a record in store table with the respective PK. so the goal is to enforce the referential integrity based of "Type".

Upvotes: 0

Views: 165

Answers (1)

Justin Cave
Justin Cave

Reputation: 231831

Not with this data model, no.

You could have separate columns, i.e. employee_id and store_id, with foreign key constraints to the appropriate tables and a check constraint that ensures that only the correct column for the particular type is entered.

There are potentially other ways to set up the data model depending on what you're actually modeling. I'm a bit hard-pressed to think of employees and stores as separate subtypes of some higher level type. But if your actual use case is something else, it potentially makes sense to have a supertype table that is the actual parent that all the tables are children of.

Upvotes: 2

Related Questions