0

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".

Shadow
  • 33,525
  • 10
  • 51
  • 64
samg
  • 311
  • 1
  • 8
  • 21
  • 2
    Lacking any additional information this _sounds_ like a poorly designed table. Sounds very much like the Entity-Attribute-Value (EAV) model, which is one of the worst design concepts ever foisted on the database world. If you were at my office asking me about this, I'd want to review your entire relational design. Design to Third Normal Form. – EdStevens Mar 05 '21 at 21:54
  • 1
    The short answer is NO. Check constraints can only constrain one row at a time - they can't even look around to see what is in other rows in the same table, much less look around at other tables. What you are asking about, in the greatest generality, is called "assertions" in the SQL standard. Alas, they are not implemented by any real-life db product - for the very good reason that the fundamental (academic) research into the theory of assertions is nowhere near finding good approaches to implementing them. That, quite aside from your question not making much sense, as pointed out by others. –  Mar 06 '21 at 00:09

1 Answers1

2

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384