How to prevent orphaned polymorphic records?

Question

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.

1) A ContactMethod row without a phonenumber and emailadress row is not orphan from a database design perspective. A ContactMethod row would be orphan if the person id field were blank or pointed to an invalid parent value. The right terminology would be childless, not orphan. 2) Foreign keys work only the other way around. There are no standard solutions in sql to handle such cases. Therefore you are reliant on implementing custom code via triggers and stored procedures. These solutions are specific to the database product you use, you need to narrow down the question to one database product. — Shadow, Nov 23 '22 at 15:20

score 3 · Accepted Answer · answered Nov 23 '22 at 18:44

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

CREATE TABLE ContactMethod(
  id integer PRIMARY KEY
  person_id integer
  priority integer
  allow_solicitation boolean,
  phone_number varchar DEFAULT NULL
  email_address varchar DEFAULT NULL 
  FOREIGN KEY(person_id) REFERENCES People(id)
  CHECK (COALESCE(phone_number, email_address) IS NOT NULL)
)

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.

CREATE TABLE ContactMethod(
  id integer PRIMARY KEY
  person_id integer
  priority integer
  allow_solicitation boolean,
  phone_number_id integer DEFAULT NULL
  email_address_id integer DEFAULT NULL 
  FOREIGN KEY(person_id) REFERENCES People(id)
  FOREIGN KEY(phone_number_id) REFERENCES PhoneNumbers(id)
  FOREIGN KEY(email_address_id) REFERENCES EmailAddresses(id)
  CHECK (COALESCE(phone_number_id, email_address_id) IS NOT NULL)
)

I adopted your second solution, where I have foreign keys in both directions, plus a CHECK constraint to ensure exactly one of them is non-null, and it solved the problem perfectly. Thank you! — sweatervest, Nov 29 '22 at 17:37

score 0 · Answer 2 · answered Nov 23 '22 at 15:28

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 Value

You can insert both records in a transaction, and make the foreign key constraint deferred. — sweatervest, Nov 29 '22 at 17:38

How to prevent orphaned polymorphic records?

2 Answers2