Mysql : foreign key relative to multiple tables

foreign keyMySQL

I'm trying to improve the database integrity adding some foreign key (it's the first time i use them).

I have some tables relative to different type of devices (different tables because the "devices" are completely different from each other), a table groups and a relation-table group_device (the same device can be in more groups).

devices_typeA

id is primary key

id | column1 | column2 | 
------------------------
1  | aaa     | bbbb    |
2  | aaa     | bbbb    |

devices_typeB

id is primary key

id | column1 | column2 | column2 | columnN | 
--------------------------------------------
1  | aaa     | bbbb    | cccc    | dddd    |
2  | aaa     | bbbb    | cccc    | dddd    |

devices_typeC

id is primary key

id | column1 | 
--------------
1  | aaa     | 
2  | aaa     |

groups

id is primary key

id | name    | 
--------------
1  | group 1 | 
2  | group 2 | 
3  | group n |

group_device

id | type_device | id_device | id_group |
-----------------------------------------
1  | typeA       | 1         | 1        | 
2  | typeA       | 1         | 2        | 
3  | typeB       | 1         | 1        | 
4  | typeC       | 2         | 3        |

Now i would like to add some foreign key to the tables group_device (in such a way that when a device is deleted, is deleted also in the table group_device [with the ON DELETE CASCADE action]). My problem is the id_device column, that is relative to multiple tables (devices_typeA.id, devices_typeB.id , devices_typeC.id).

Best Answer

FOREIGN KEYs have severe limitations. I think you have exceeded them.

Complex data structures cannot depend on FKs for integrity, your code is required.

Suggest that you build Stored Routines and/or subroutines in your application language, and encapsulate the integrity constraints there. And build a clean API into the routines to make it easy for the clients to achieve their goals while the routine achieves the integrity goals.

Related Solutions

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

Foreign key relationships are to enforce data integrity, not for query performance, that is what indexes are for. Also note that InnoDB creates an index on each column with a foreign key relationship.

However I would recommend having the foreign key relationships to ensure that the data is always valid, especially when updating and deleting, which may become significant when you have to start archiving data.

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

Composite primary key from multiple tables / multiple foreign keys

Related Question