MySQL Foreign key with duplicate values constraints fails

Question

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.

Could you please copy/paste the request used to add this `FOREIGN KEY` ? — zessx, Jul 03 '14 at 11:44
Please post `show create table User` and `show create table Customers`. — Barmar, Jul 03 '14 at 11:45
@DalaiLama It is. Could you check you havn't got any `user_id_fk` already set on this table ? — zessx, Jul 03 '14 at 11:48
It is possible that foreign keys cannot be duplicated in a different table? I kinda remember doing it several times in the past and never had this issue — Dalai Lama, Jul 03 '14 at 11:58
You can't use the same name for the foreign key constraint in different tables. — Barmar, Jul 03 '14 at 12:02
See http://stackoverflow.com/questions/15014592/mysql-error-1022-when-creating-table — Barmar, Jul 03 '14 at 12:03

score 1 · Accepted Answer · answered Jul 03 '14 at 12:06

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.

MySQL Foreign key with duplicate values constraints fails

1 Answers1