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)
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
);