CODEWITHSUNDEEP
mysql
Dalai Lama
Dalai Lama

Reputation: 399

MySQL Foreign key with duplicate values constraints fails

I have a user table with id and name

User Table:
+----+------+
| id | Name |
+----+------+
|  1 | Dan  |
+----+------+

And a customer Table, each user can have several customers.

Customers Table:
+----+---------------+-------------+-------+
| id | customer_id   | user_id     | Name  |
+----+---------------+-------------+-------+
|  1 |    1          |       1     | Rito  |
|  2 |    2          |       1     | Plz   |
+----+---------------+-------------+-------+

I've made user_id an index, and when I try put it as a foreign key with on delete cascade it fails and claims that it is duplicated. Both keys are same type, both tables are InnoDB!

This is my request:

ALTER TABLE `customers` 
ADD CONSTRAINT `user_id_fk`
  FOREIGN KEY (`user_id`)
  REFERENCES `users` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE

This is my user table creation code:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(35) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This is the customer table creation code:

SQL Statement:
CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `Name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_customer_user` (`customer_id`,`user_id`),
  KEY `user_id_idx` (`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

"ERROR 1022: Can't write; duplicate key in table"

My question is, how can I do that delete cascade in this table (without triggers) using foreign keys?

Solved:

Apparently the duplicate was related to the constrain name and not the values of the data inside the table.

For future readers, You cannot use the same constraint name over the entire database! not just per table.

Upvotes: 0

Views: 2447

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

The error ERROR 1022 states that there is a duplicate key in table but what it doesn't state is that the duplicate key is related to the key name that your are creating over the entire database. So it is possible that one of the keys you are creating on your table (unique_customer_user, user_id_idx, user_id_fk) is already present on some other table of your database.

Look at that or just change the names of the keys and try again.

Upvotes: 1

Related Questions