CODEWITHSUNDEEP
mysqlsql
dexteran
dexteran

Reputation: 95

Integrity of foreign keys on a table where first FK is a PK on one table but not one the second

CREATE TABLE table1
(
   id INT NOT NULL PRIMARY KEY,
   some_data VARCHAR(100) NOT NULL
)

CREATE TABLE table2
(
   id INT NOT NULL PRIMARY KEY,
   table1_id INT NOT NULL,
   some_data VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table2_table1 FOREIGN KEY (table1_id) REFERENCES table1(id)
)

CREATE TABLE table3
(
   id INT NOT NULL PRIMARY KEY,
   table1_id INT NOT NULL,
   table2_id INT NOT NULL,
   some_data VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table3_table2 FOREIGN KEY (table2_id) REFERENCES table2(id)
   CONSTRAINT fk_table3_table1 FOREIGN KEY (table1_id) REFERENCES table1(id)
)

While the fk constraints on table3 maintains the integrity of presence/validation of the table1_id and table2_id values, I'm looking for a way to also check for the integrity of table1_id to be the table1_id in table2 where table2(id) is table2_id. Is there a way to check for this constraint in MySQL?

Upvotes: 0

Views: 50

Answers (2)

The Impaler
The Impaler

Reputation: 48770

In relational databases the foreign keys are not checked transitively. The only way of ensuring the FKs are well connected as you want to, is to use composite keys. For example:

CREATE TABLE table1 (
   id INT NOT NULL PRIMARY KEY,
   some_data VARCHAR(100) NOT NULL
)

CREATE TABLE table2 (
   table1_id INT NOT NULL,
   id INT NOT NULL,
   some_data VARCHAR(30) NOT NULL,

   PRIMARY KEY (table1, id), -- composite PK
   CONSTRAINT fk_table2_table1 FOREIGN KEY (table1_id) REFERENCES table1(id)
)

CREATE TABLE table3 (
   table1_id INT NOT NULL,
   table2_id INT NOT NULL,
   id INT NOT NULL PRIMARY KEY,
   some_data VARCHAR(30) NOT NULL,

   PRIMARY KEY (table1_id, table2_id, id), -- optional composite PK
   CONSTRAINT fk_table3_table2 FOREIGN KEY (table1_id, table2_id) 
     REFERENCES table2 (table1_id, id) -- references table_2 composite PK
)

Upvotes: 1

M. Arnold
M. Arnold

Reputation: 425

The three tables are not normalized enough and would actually lead to duplication of data. You do not need to reference table1_id in table3 since it is already referenced in table2. A table2 record already references table1_id. You just need to remove table1_id in table3 plus the Constraint fk_table3_table1 and all will be well.

Upvotes: 0

Related Questions