Creating Duplication constraint on mysql table

Question

I have the following table, I am trying to create a constraint on the table that states that the Ter_ID column can have the same Ter_ID repeating however the corresponding Status cannot be the same at anytime, so if for example one is trying to update Ter_ID 100P Status from U to A it wouldn't go through seeing that there is already a row with the corresponding data.

Ter_ID  Status  Address
100P    A   Road1
100P    U   Road2
200R    A   Road2

jordeu · Accepted Answer · 2014-09-25 14:26:39Z

1

You need to add a composed unique index using the two columns.

Example:

ALTER TABLE `tablename` ADD UNIQUE `unique_index`(`Ter_ID`, `Status`);

Be aware of the different NULL behaviour depending of the MySQL engine that you are using. From the official MySQL documentation:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

edited Sep 25, 2014 at 14:26

answered Sep 25, 2014 at 14:20

jordeu

6,8171 gold badge21 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating Duplication constraint on mysql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related