Altering existing unique constraint

Question

I have a table that was defined like this:

CREATE TABLE `Message` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `user_id` integer NOT NULL,
    `user_to` integer NOT NULL,
    `top_num` integer NOT NULL,
    `priority` smallint NOT NULL,
    `error` varchar(120) NOT NULL,
    UNIQUE (`user_id`, `user_to`, `top_num`)
);

Later, I added another column to it, msg_type, like this:

ALTER TABLE Message ADD COLUMN msg_type SMALLINT(6) NOT NULL DEFAULT 0;

However, I have come to realize that I need to change my original UNIQUE constraint to include msg_type. I tried running

ALTER TABLE Message 
ADD UNIQUE INDEX (`user_id`, `user_to`, `top_num`, `msg_type`);

but INSERTs into my table still fail, and the error message indicates that that is because the old uniqueness constraint fails.

When I call describe Messages in mysql I see the following:

+-----------------+----------------------+------+-----+---------+----------------+
| Field           | Type                 | Null | Key | Default | Extra          |
+-----------------+----------------------+------+-----+---------+----------------+
| id              | int(11)              | NO   | PRI | NULL    | auto_increment |
| user_id         | int(11)              | NO   | MUL | NULL    |                |
| user_to         | int(11)              | NO   | MUL | NULL    |                |
| top_num         | int(11)              | NO   | MUL | NULL    |                |
| priority        | smallint(6)          | NO   |     | NULL    |                |
| error           | varchar(120)         | NO   |     | NULL    |                |
| msg_type        | smallint(6)          | NO   |     | 0       |                |
+-----------------+----------------------+------+-----+---------+----------------+

which makes it seem like msg_type really isn't part of the constraint... How can I alter the constraint that the table was defined with, short of recreating the table?

Read [How to change the foreign key referential action?](http://stackoverflow.com/questions/3359329/how-to-change-the-foreign-key-referential-action-behavior/14381227#14381227), Similarly you will change your unique constraint. — Grijesh Chauhan, Feb 14 '14 at 11:16
Note when you drops constraint then only constrict is deleted, column is not deleted. it is two step work in MySQL. — Grijesh Chauhan, Feb 14 '14 at 11:17

Grijesh Chauhan · Accepted Answer · 2023-07-18T09:28:03.323

22

As in previous answer to change foreign key constraint use steps:

Step 1: Drop old constraint:

ALTER TABLE `Message` DROP INDEX `user_id`;

Step 2: Add new:

ALTER TABLE `Message` ADD UNIQUE INDEX (
         `user_id`, 
         `user_to`, 
         `top_num`, 
         `msg_type`);

Use SHOW CREATE TABLE to know name of constraint:

mysql> SHOW CREATE TABLE `Message` ;

| Message | CREATE TABLE `Message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_to` int(11) NOT NULL,
  `top_num` int(11) NOT NULL,
  `priority` smallint(6) NOT NULL,
  `error` varchar(120) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`user_to`,`top_num`)
--           ^^^^^^^^^  name 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

If you checks:

mysql> SHOW INDEX FROM `Message`;

Key_name is user_id that is first argument in UNIQUE (user_id ....

Suppose if you write:

ALTER TABLE `Message` ADD UNIQUE INDEX ( 
      `user_to`, 
      `user_id`, 
      `top_num`, 
      `msg_type`);

Then you have to drop using user_to as:

  ALTER TABLE `Message` DROP INDEX `user_to`;

edited Jul 18 '23 at 09:28

answered Feb 14 '14 at 12:05

Grijesh Chauhan

57,103
20
141
208

1

Thank you! This is incredibly detailed and helpful. Particularly the "SHOW CREATE TABLE to know name of constraint" advice, that's super useful. My situation is complicated by the fact that some of these are foreign keys, but I'll figure it out. – Catherine Feb 14 '14 at 12:29
1

@Catherine Your Welcome!.. Remember when you delete constraint then column is not deleted so in this particular operation you don't need to bother if some filed are foreign keys. If you have these referenced by some table then you can disable the fk constraint `SET FOREIGN_KEY_CHECKS = 0;` for time being. – Grijesh Chauhan Feb 14 '14 at 12:39
**WARNING**: If the database is currently serving queries to real users, you From @[Brandon](https://stackoverflow.com/users/828709/brandon) **WARNING**: you are risking the uniqueness constraint being violated between the time the old index is dropped and the time the new index is created. If possible, you should first add the new unique index, THEN drop the old index. – Grijesh Chauhan Jul 18 '23 at 09:30

score 1 · Answer 2 · edited Oct 25 '19 at 09:40

1

This is because you are adding unique index. Please first drop unique index and then add unique constraint.

-- you have to drop each index one by one.
ALTER TABLE Message DROP UNIQUE INDEX user_id;

and now add unique constraint.

ALTER TABLE Message ADD CONSTRAINT uc_message UNIQUE (`user_id`, `user_to`, `top_num`, `msg_type`);

edited Oct 25 '19 at 09:40

Lee Han Kyeol

2,371
2
29
44

answered Feb 14 '14 at 12:46

Aftab Ahmed

1,727
11
15

score 0 · Answer 3 · edited May 23 '17 at 12:34

0

This is because you haven't deleted the first unique constraint you have created. Right now, you have two unique constraints on your table.

To delete a unique constraint, have a look at this post Dropping Unique constraint from MySQL table

edited May 23 '17 at 12:34

Community

1
1

answered Feb 14 '14 at 11:14

Saurabh Goyal

51
3

score 0 · Answer 4 · answered Feb 14 '14 at 11:31

0

This is because you are adding unique index. Please first drop unique index and then add unique constraint. DROP INDEX index_name ON table_name

and now add unique constraint.

    ALTER TABLE Message 
     ADD CONSTRAINT uc_message UNIQUE ((`user_id`, `user_to`, `top_num`, `msg_type`);)

answered Feb 14 '14 at 11:31

Aftab Ahmed

1,727
11
15

How to drop that is the question. – Grijesh Chauhan Feb 14 '14 at 11:36
DROP INDEX index_name ON table_name – Aftab Ahmed Feb 14 '14 at 11:56
ALTER TABLE Message DROP UNIQUE INDEX user_id you have to drop each index one by one. – Aftab Ahmed Feb 14 '14 at 11:59
you are correct you just need it to your answer. then I will vote you. And delete your other one line answer – Grijesh Chauhan Feb 14 '14 at 12:14

Altering existing unique constraint

4 Answers4