Implement foreign key constraint when there are several parent tables

It might be the case that my initial design is flawed, but let's start from there and see whether there is a good enough approach.

I have two entities, say, A and B that are very similar but not identical so the sets of columns are different. For those reasons, there are two distinct tables, A and B.

That said, I would like to create another table that should describe both A and B with a set of fields that are applicable to both A and B. Let's call this table entity_settings.

Its schema is not intuitive to figure out:

-
My first idea is to use some kind of entity_type flag and entity_id from either A or B depending on the type with a UNIQUE constraint on (entity_type, entity_id). Such a definition makes it impossible to have a normal foreign constraint because this entity_id consists of ids from two tables.

-
Another option is having A_id and B_id that can be null but reference the ID fields from the corresponding tables. This makes sense and provides referential integrity, but inserts into this table are going to be pretty clumsy.

-
The only option I am also considering is having two separate tables for A and B so that each table has its own child. This looks a bit wasteful in terms of the number of tables, but is probably the cleanest option in terms of design.

As almost always, I have a feeling that there is something I am missing and would highly appreciate it if someone could suggest any other options.

EDIT:

In the second option, it also makes sense to add a CHECK constraint like CHECK((A_id IS NOT NULL AND B_id IS NULL)) OR (A_id IS NULL AND B IS NOT NULL))

You are not missing anything, this is a known tough problem in relational databases. The concept doesn't lend itself to models like that naturally.

Your solution 2. is a good one, with the check constraint.

Solution 3. is also possible. Perhaps you can use partitioning, so that you can have a single partitioned table entity_settings, and the partitions have foreign keys to A and B. If that doesn't cause problems with your queries or other parts of the system, it's a decent solution.

StackExchange Database Administrators Q#317776, answer score: 4