CODEWITHSUNDEEP
mysqlsqlforeign-keysinnodb
Sebi2020
Sebi2020

Reputation: 2159

Multiple foreign key constraints to same table and column fails on update

I've created two tables to do mappings between users. First for users and second for user-mappings. Deletion of users work well, but if I try to update the user id the foreign key constraints from the mapping table fail (without a helpful error output).

CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(55),
PRIMARY KEY (`id`)
);

CREATE TABLE user_map (
map_id INT NOT NULL AUTO_INCREMENT,
user_a INT,
user_b INT,
  PRIMARY KEY (`map_id`),
  UNIQUE KEY `one_way` (`user_a`,`user_b`),
  UNIQUE KEY `other_way` (`user_b`,`user_a`),
  CONSTRAINT `acc_connections_ibfk_1` FOREIGN KEY (`user_a`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `acc_connections_ibfk_2` FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)

Example Data:

INSERT INTO user (name) VALUES ("User A");
INSERT INTO user_map (user_a,user_b) VALUES (1,1);

If I try to update the user id afterwards I get the following error:

Cannot add or update a child row: a foreign key constraint fails
(`test_db`.`user_map`, CONSTRAINT `user_map_ibfk_2`
FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

DB Fiddle (Demo)

Interestingly deleting the parent row (user table) succeeds without an error. What am I doing wrong? I see no reason why this should fail.

Upvotes: 1

Views: 319

Answers (1)

forpas
forpas

Reputation: 164204

I don't know if this is a bug or intended behavior.

As a workaround, if your version of MySql is 8.0.13+, which supports Functional Key Parts, you can use 1 UNIQUE KEY (to check the uniqueness of the combination of the 2 columns) instead of the 2 keys and the UPDATE statement will work:

CREATE TABLE IF NOT EXISTS user_map (
map_id INT NOT NULL AUTO_INCREMENT,
user_a INT,
user_b INT,
  PRIMARY KEY (`map_id`),
  UNIQUE KEY unk_users((LEAST(`user_a`,`user_b`)), (GREATEST(`user_a`,`user_b`))),
  CONSTRAINT `acc_connections_ibfk_1` FOREIGN KEY (`user_a`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `acc_connections_ibfk_2` FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
);

See the demo.

Upvotes: 1

Related Questions