CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
BIBD
BIBD

Reputation: 15404

Can I set up an integrity constraint to multiple tables on one column?

I have a table which contains a list of components associated with a device.

dev_comps
=========
dev_id comp_type comp_id
------ --------- -------
     1         A    1234
     1         A    1237
     1         A    1238
     1         B    5678
     1         C    1234
     2         A    1235

(yes I can have multiples of each kind of component on the device, otherwise I'd just attach them to the device table instead of having a junction table)

I also have several table for the types of components (which of course all have a little bit different options each other).

comp_a
======
comp_a_id color style
--------- ----- -----
     1234   red glass
     1235  blue  wood

comp_c
======
comp_c_id style length
--------- ----- ------
     1234 glass      5
     1235  wood      7

Is it possible to set up an integrity constrain between dev_comps.comp_id and comp_a.comp_a_id; and dev_comps.comp_id and comp_c.comp_c_id; etc.?

In general I don't want my app/users to be able to create an entry in dev_comps where I don't have a matching comp_id in the corresponding comp_* table.

Or, is there a better way to do this?

Edit

I've come to the conclusion that what I'm really doing here is Column Overloading. Which as I recall from my old Intro to DBs course is generally considered a poor design. Is there a clean way out of this?

Upvotes: 0

Views: 69

Answers (1)

Justin Cave
Justin Cave

Reputation: 231721

No, you can't have a foreign key constraint that refers to one of two possible parent tables. There are two common approaches to this sort of issue:

  1. You can combine all the comp_* tables into a single table, adding in a comp_type column. Presumably, this would mean that many attributes would be NULL for every row since each type of component only has a small number of the available attributes. You can add constraints that ensure that the appropriate set of columns are NULL or NOT NULL depending on the comp_type but you'll waste a bit of space. On the other hand, storing NULL values, particularly at the end of the row, is pretty cheap.

  2. You can create a master_component table that just has the comp_id and comp_type. The various comp_* tables could have foreign keys that point to that table (though you'd want comp_type to be added at least as a virtual column). And dev_comps could point at the master_component table.

Of course, there are other options depending on the specifics of the problem. If there are a large number of components with lots of different attributes, such that combining tables would produce a master_component table with hundreds of columns, it may make more sense to have a single master_component table with a component_attribute table that stores attributes as rows rather than as columns. But that's more of a specialized case not something I'd start with.

Upvotes: 2

Related Questions