I have a database of polymorphic structure: a "base" type table and two "derived" types:
CREATE TABLE ContactMethod(
id integer PRIMARY KEY
person_id integer
priority integer
allow_solicitation boolean
FOREIGN KEY(person_id) REFERENCES People(id)
)
CREATE TABLE PhoneNumbers(
contact_method_id integer PRIMARY KEY
phone_number varchar
FOREIGN KEY(contact_method_id) REFERENCES ContactMethod(id)
)
CREATE TABLE EmailAddresses(
contact_method_id integer PRIMARY KEY
email_address varchar
FOREIGN KEY(contact_method_id) REFERENCES ContactMethod(id)
)
I want to prevent orphaned ContactMethod
records from existing, that is, a ContactMethod
record with neither a corresponding PhoneNumber
record nor an EmailAddress
record. I’ve seen techniques for ensuring exclusivity (preventing a ContactMethod
record with both a related PhoneNumber
and EmailAddress
), but not for preventing orphans.
One idea is a CHECK constraint that executes a custom function that executes queries. However, executing queries via functions in CHECK constraints is a bad idea.
Another idea is a View that will trigger a violation if an orphaned ContactMethod
record is added. The "obvious" way to do this is to put a constraint on the View, but that’s not allowed. So it has to be some sort of trick, probably involving an index on the View. Is that really the best (only?) way to enforce no orphans? If so, what is a working example?
Are there other ways? I could get rid of ContactMethod
table and duplicate shared columns on the other two tables, but I don’t want to do that. I’m primarily curious about capabilities available in MySQL and SQLite, but a solution in any SQL engine would be helpful.
2
Answers
A newly inserted
ContactMethod
will always be orphaned until you insert a phone number or an e-mail address. So, you cannot test the condition at insert.Instead, you could insert contact information with a stored procedure having an optional phone number and optional e-mail parameter in addition to the base information. The base record would only be inserted if at least one of the two has a non-null value.
Then create a delete trigger when a phone number or an e-mail address is deleted, to either delete the
ContactMethod
record when no related record exist anymore or to raise an exception as shown in Alter a Delete Trigger to Check a Column ValueThe simplest solution would be to use single table inheritance. So both the contact methods are optional (that is, nullable) fields in the ContactMethod table, but you add a CHECK constraint to ensure at least one of these has a non-null value.
Another solution that supports polymorphic associations is to reverse the direction of foreign key. Make ContactMethod have a one nullable foreign key for each type of associated method. Use a CHECK to make sure at least one has a non-null value. This works because you don’t allow multiple emails or phones per row in ContactMethod. It does mean if you add a different type of contact (e.g. Signal account), then you’d have to add another foreign key to this table.