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?

Answer 1

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

Answer 2

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