CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
AnilM3
AnilM3

Reputation: 311

Database constraints based on relationship dependencies

I am trying to model a tricky case in the database schema in order to avoid possible inconsistencies. The following diagram roughly describes the current situation, there are 3 tables which interact with each other, table1 and table 2 have an N:M relationship, table2 and table3 have another N:M relationship, table1 and table3 have a 1:N relationship. I need to somehow add a constraint such there can only be relationships between table2 and table3 when there also exists a relationship between table2 and the table1 which is foreign key of that particular instance of table3.

enter image description here

As an example, let's suppose we have the following tables:

enter image description here

Given the constraint I want to impose, the first row in table3_has_table2 is valid, as table3[0x000020] has table1[0x0000A] as FK and table2[0x00010] has a an entry in table1_has_table2 with table1[0x0000A], but the second row in table3_has_table2, is not valid as table2[0x00011] has no relationship with table1[0x0000A] in table1_has_table2.

Upvotes: 3

Views: 870

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

I think your last foreign key reference is referencing the wrong table. I wrote this in PostgreSQL. For MySQL, you'll just need to move the inline constraints into separate constraint clauses.

I think these first three tables are the same as yours. (But with shorter names. If you're going to use meaningless names in your questions, at least make them short.)

create table t1 (
  t1_id integer primary key
);

create table t2 (
  t2_id integer primary key
);

create table t1_has_t2 (
  t1_id integer not null references t1 (t1_id),
  t2_id integer not null references t2 (t2_id),
  primary key (t1_id, t2_id)
);

The table "t3" is a little different. The unique constraint looks redundant, but it's not. It lets the pair of columns be the target of a foreign key reference.

create table t3 (
  t3_id integer primary key,
  t1_id integer not null references t1 (t1_id),
  unique (t3_id, t1_id)
);

And the last table, "t3_has_ts", is different enough that it needs a different kind of name. It has overlapping foreign key constraints.

create table t3_has_ts (
  t3_id integer not null,
  t2_id integer not null,
  t1_id integer not null,
  foreign key (t1_id, t2_id) references t1_has_t2 (t1_id, t2_id),
  foreign key (t3_id, t1_id) references t3 (t3_id, t1_id),
  primary key (t3_id, t2_id, t1_id)
);

I used integers for the integer columns.

insert into t1 values (10), (11);
insert into t2 values (16), (17);
insert into t3 values (32, 10);

insert into t1_has_t2 values (10, 16);
insert into t1_has_t2 values (11, 17);

-- This is the row you said should succeed.
insert into t3_has_ts values (32, 16, 10);

-- And this one should fail.
insert into t3_has_ts values (32, 17, 11);

And it does indeed fail. The PostgreSQL error message says that 

(32, 11) is not present in table "t3"

which seems correct.

Upvotes: 2

Related Questions