on delete set null constraint on null field for c:n with combined PK

Question

so I'm having quite a hard time understanding why this code snippet gives me an Error: 150 "Foreign key constraint is incorrectly formed"when trying to create the FK

CREATE TABLE T1 (
  t1ID INT AUTO_INCREMENT,
  /* ... unrelated columns */
  PRIMARY KEY (t1ID)
);

CREATE TABLE T2 (
  t3fk INT NOT NULL,
  t1fk INT,
  /* ... unrelated columns */
  CONSTRAINT t2ID PRIMARY KEY (t3fk,t1fk),
  FOREIGN KEY (t1fk) REFERENCES T1(t1ID) ON DELETE SET NULL
);

What I'm trying to do is create a c:n relation between t1 and t2 with t2 having a combined PK where (t3fk:null) is a valid identifier

I've searched quite a while for an answer now but I only find results like these, where the FK-column is set to not accept the null value:

MySQL - Foreign key on delete set null in not null field

MySQL: error 150 using ON UPDATE SET NULL and ON DELETE SET NULL, why?

I'm using an InnoDB.

Thank you for your attention (also feedback for future questions is welcome)

nacho · Accepted Answer · 2017-06-13T06:44:10.837

This happens just because you set t1fk as part of the primary key in table t2, and primary keys must be UNIQUE & NOT NULL. If you delete the referenced value you would have (t3fk:null) as you said, but it´s not posible. You think you have the following data

T1    t1ID   5
      t1ID   6
      t1ID   7
T2    t3fk  2
      t1fk  5

 T2   t3fk 2
      t1fk 6

If you delete from t1 the numbers 5&6, you would have in t2 two rows with the same value (2,null), and this it´s not posible because it´s a primary key that must be UNIQUE and NOT NULL.

If t1ID is part of a primary key, you cannot let it to be null.

To solve it you can make an artificial primary key (autoincrement) and then a key that allows duplicates for (t3fk,t1fk) this way

CREATE TABLE T1 (
  t1ID INT AUTO_INCREMENT,
  /* ... unrelated columns */
  PRIMARY KEY (t1ID)
);

CREATE TABLE T2 (
  t2fk int auto_increment,
  t3fk INT NOT NULL,
  t1fk INT,
  /* ... unrelated columns */
  CONSTRAINT t2ID primary key (t2fk),
  KEY (t3fk,t1fk),
  FOREIGN KEY (t1fk) REFERENCES T1(t1ID) ON DELETE SET NULL
);

makes sense thank you - can you elabore what the suggested solution for a c:n solution would be? (Creating a artificial key maybe?) — Simon, Jun 12 '17 at 18:55

on delete set null constraint on null field for c:n with combined PK

1 Answers1