Let us say that I have a table A, which is considered to be a "superclass" table to tables B1, B2, ..., Bn.

This relationship is disjunctive and total. That is, every time an entry is created in table A, then a corresponding entry must be created in exactly one of the tables B1, B2, ..., Bn.

Note that each entry in B1, ..., Bn has a foreign key into its corresponding entry in A.

My question is how to enforce this in MySQL.

My potential idea is to have an ENUM column (let's call subclass) in table A that corresponds to the subclass table to create. Then, there would be triggers that activate in the following situations:

1. An entry is inserted into table A.
2. One or more entries' ID attributes are modified in table A.
3. One or more entries' subclass attributes are modified in table A.

Upon the activation of a trigger, depending on the value of subclass, the user is prompted to create an entry into the corresponding table in B1, B2, ..., Bn.

I would provide a sample, but I am having quite a bit of trouble even finding how the user could specify the values in the inserted entry into one of the tables B1, B2, ..., Bn. I am unsure if this problem is even solvable using triggers due to this issue.

Edit: This question is not a duplicate of this question because neither the question nor answer discusses the main problem mentioned in the question. That is, the constraint that every time an entry is created in the the table A, a corresponding entry must be created in one of the "subclass" tables B1, ..., Bn

Edit 2: In my particular situation, there is a patient table, which has "subclass" tables adult_patient and child_patient.

The patient table carries all attributes that both adult_patient and child_patient have such as first name, last name, date of birth, etc.

The adult_patient table has some of its own attributes like primary_phone and primary_email. The child_patient table links with a guardian table where each guardian row contains primary_phone, primary_email, etc.

CodePudding user response：

You could put n foreign keys into a referencing bn each. In a check constraint, check that exactly one of them is not NULL. That enforces that a corresponding record in exactly one bi must exist.

CREATE TABLE b1
             (id integer,
              PRIMARY KEY (id));

CREATE TABLE b2
             (id integer,
              PRIMARY KEY (id));

CREATE TABLE b3
             (id integer,
              PRIMARY KEY (id));

...

CREATE TABLE a
             (id integer,
              b1 integer,
              b2 integer,
              b3 integer,
              ...
              PRIMARY KEY (id),
              FOREIGN KEY (b1)
                          REFERENCES b1
                                     (id),
              FOREIGN KEY (b2)
                          REFERENCES b2
                                     (id)
              FOREIGN KEY (b3)
                          REFERENCES b3
                                     (id),
              ...
              CHECK (b1 IS NOT NULL
                     AND b2 IS NULL
                     AND b3 IS NULL
                     ...
                      OR b1 IS NULL
                         AND b2 IS NOT NULL
                         AND b3 IS NULL
                         ...
                       OR b1 IS NULL
                          AND b2 IS NULL
                          AND b3 IS NOT NULL
                          ...));
         

CodePudding user response：

Plan A:

Remove FOREIGN KEY constraints. Replace them with INDEXes where needed.

A FK is two things dynamically checked constraint and an index to make the constraint checking 'fast'. Sub-classing is not friendly with constraint checking.

Plan B:

Don't try to use "sub-classing" in SQL. It is so messy as to [usually] not be worth doing.

If you provide realistic table and column names, we might be able to help you redesign the schema.

Plan C:

Roll all the "patient" info into a single table. There does not seem to be enough difference between "adult" and "child" to warrant the hassle of sub-classing.

• Use NULLs where there is no value.
• "Guardian" becomes "Caretaker" for an elderly person.
• Even a "child" can have a preferred phone -- to reach the parent. That is, I would copy it into this table. When calling, say "I am calling about "John Doe" -- This lets the parent/caretaker understand what is going on.