Have a foreign key reference multiple tables?

Question

I have 3 tables:

Shirts(model, color)
Gloves(model, color)
Socks(model, color)

Where model is primary key in all 3 tables

Now I need to make another table:

Products (model, price)

So, in products I need to get all models from my first 3 tables, in one single column. How can I do that?

Answer 1

In response to suggestion of LittleFoot, one would want 'date' and 'price' for products in a table referencing 'product' table. One would otherwise lose that information when updated, making record of date irrelevant. 'date' and 'price' are 'historical data' (like a person's salary) subject to change over time. Such data is commonly maintained in a separate table.

Answer 2

One solution to accomplish what is stated is to use record keys independent of data record. We use a GUID for each record. Foreign key constraint to one of several tables is accomplished by keeping a table of "keys" to which foreign key constraint is made. Each record in tables contain a unique key that must exist in key table, possibly accompanied by a code to indicate owning table. Where a foreign key defining a relation between items in other tables would reference a key from any of several tables the reference key table. Records in table defining relation would each have their own key in key table, as well. We have a parameterized stored procedure to insert records that obtains a new key value if one is not present in submitted record. New key is inserted in key table, then new record is inserted in respective table. An existing key would cause an exception on insert (should be an update.)

Note that in given case problem is, as previously stated, one of design. One would use a single table of products, with a code defining the type of product. Any reference to a product item is a foreign key to one table. Categories can be 'viewed' by defining a view that selected items with a given category code or codes. (Most often why people mistakenly create multiple tables.)

This same problem is encountered in accounting of purchases. Some purchases are by 'order', some are 'direct' (or immediate). One has to be able to track orders. Solution is to defined a purchase table with 'category' code (e.g., 'O' or 'P') constraint. Then define tables to contain any data specific to order (e.g., vendor order number) or direct purchases (e.g., receipt image file) referencing purchase record.

This same problem is encountered in accounting of inventory resulting from purchases. Solution is same. One creates tables to contain detail unique to each item category. E.g., 'gem' vs 'finding' for items used to make jewelry.

Answer 3

If you mean in an environment like phpMyAdmin, MySQLWorkbench etc. you can just manually go to each product table 'Shirt','Gloves','Socks' and add a reference/foreign key of the field 'Model' to the the field 'Model' in the table 'Product'. The way to do this depends entirely on the programm you are using. Note that ofcourse you should create the new Table either manually or by SQL code before you do this.

There is no problem for multiple tables to point to same other table via foreign keys.

If you want to write SQL code to this you can use something like this, which is the other way around (use three foreign keys in the table 'Product', one for each other table). Note that the tables Socks,Shirts and Gloves must have already been created.

DROP TABLE IF EXISTS `Product` ;
CREATE TABLE `Product` (
`Color` varchar(8),
`Model` varchar(14),
PRIMARY KEY(`Model`),
FOREIGN KEY('Model') REFERENCES `Socks`(`Model`) ON DELETE CASCADE,
FOREIGN KEY('Model') REFERENCES `Gloves`(`Model`) ON DELETE CASCADE,
FOREIGN KEY('Model') REFERENCES `Shirts`(`Model`) ON DELETE CASCADE
) ;

The way you want is something like this (done three times one for each table). Note that as i said before the table Product must have been already created.

DROP TABLE IF EXISTS `Socks` ;
CREATE TABLE `Socks` (
`Color` varchar(8),
`Model` varchar(14),
PRIMARY KEY(`Model`),
FOREIGN KEY('Model') REFERENCES `Product`(`Model`) ON DELETE CASCADE
);

Answer 4

A foreign key can be created from the Products table to each of the other tables. However, creating a foreign key from each of the other tables (Shirts, Gloves, Socks) is not possible (unless each Product exists in all three tables). A foreign key from Shirts, Gloves, and Socks would basically say: before an insert into the Products table ensure that there is a record in each of the three other tables with the same key.

As suggested by the other posters, it is probably better to consider a slightly different design (e.g. add a type column to the products table).

Also, by convention table names should be singular (Product, Shirt, Glove, Sock).

Answer 5

In my opinion, you've designed it wrong. Suggestion (as a comment under the question, saying that products should be referenced by 3 another tables) is - again, in my opinion - wrong.

You shouldn't create separate tables for shirts, gloves or socks. What will you do when you start selling hats or trousers or shoes? Will you create new tables for all of those? Of course not - those are just clothes (products) types.

So - create one table that contains all types; when new type appears, simply add a new row into that table and reference it from the products table.

Something like this:

SQL> create table product_type
  2    (id_type       number primary key,
  3     name          varchar2(30)
  4    );

Table created.

SQL> create table products
  2    (id_product    number primary key,
  3     id_type       number references product_type,
  4     color         varchar2(20),
  5     price         number
  6    );

Table created.

SQL> insert into product_type
  2    select 1, 'shirt' from dual union all
  3    select 2, 'glove' from dual union all
  4    select 3, 'socks' from dual;

3 rows created.

SQL> insert into products
  2    -- shirt
  3    select 100, 1, 'red',  100 from dual union all
  4    -- gloves
  5    select 101, 2, 'blue', 150 from dual;

2 rows created.

SQL>

Here come the shoes:

SQL> insert into product_type
  2    select 4, 'shoes' from dual;

1 row created.

SQL> insert into products
  2    select 113, 4, 'brown', 400 from dual;

1 row created.

SQL>

Conclusion: read about normalization.

---

If someone says that "colors should be in a separate table", well - perhaps, that wouldn't be a bad idea either. Furthermore, does the products table have to be expanded by a date column (which would show what price was valid at certain period)? Not a bad idea either. There are numerous options you can include into the model. I just tried to point you into the right direction.