Geek Answers Handbook

Modify an existing unique constraint

I have a table that was defined as follows:

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 msg_type column to it:

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

However, I realized that I needed to change the original UNIQUE constraint to include msg_type . I tried to run

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

but the INSERT in my table is still not working, and the error message indicates that this 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 is not part of the constraint ... How can I change the constraint defined by the table, except to recreate the table?

+6
source share
4 answers

As in the previous answer to change foreign key constraint , follow these steps:

Step 1: Remove the previous restriction:

 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 find out the name of the 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 check:

 mysql> SHOW INDEX FROM `Message`;

Key_name is user_id , which is the first argument to UNIQUE (user_id ....

Suppose you write:

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

Then you need to abandon the use of user_to as:

  ALTER TABLE `Message` DROP INDEX `user_to`;
+8
source

This is because you did not remove the first unique constraint that you created. Right now, you have two unique constraints on your table.

To remove a unique constraint, see this post Removing a unique constraint from a MySQL table

0
source

This is because you are adding a unique index. First remove the unique index and add a unique constraint. DROP INDEX index_name ON table_name

and now add a unique constraint.

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

This is because you are adding a unique index. First remove the unique index and add a unique constraint.

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

and now add a unique constraint. ALTER TABLE Message ADD CONSTRAINT uc_message UNIQUE (( user_id , user_to , top_num , msg_type );)

0
source

More articles:


All Articles