how to use UNIQUE index correctly?

问题:

i have 4 fields at DB.i set them become cant duplicate entry.They are:

1. Model     Varchar(14)     Unique
2. Serial    varchar(8)      Unique
3. Lot       varchar(5)      Unique
4. Line      char(5)         Unique


                    Model         Serial             Lot             Line
First data        remocon         x0001              033a            fa 01

and if i have inputed same data it can't recorded.

 remocon         x0001              033a            fa 01

but how to make this data success to input if i type like:

remocon        x0002        033a            fa 01

and i want the result like:

 Model         Serial             Lot             Line
remocon         x0001             033a            fa 01
remocon         x0002             033a            fa 01

回答1:

You need add unique constraint for all fields, not for each, i.e.

UNIQUE(Model, Serial, Lot, Line)

solution:

CREATE TABLE YourTable
(
 Model     Varchar(14)     NOT NULL,
 Serial    varchar(8)      NOT NULL,
 Lot       varchar(5)      NOT NULL,
 Line      char(5)         NOT NULL,
 unique    (model, serial, lot, line) 

)

for existing table:

 alter table YourTableName drop index model;
 alter table YourTableName drop index serial;
 alter table YourTableName drop index lot;
 alter table YourTableName drop index line;
 alter table YourTableName add unique (model, serial, lot, line); 

回答2:

If you create a Unique constraint for each field, each field needs to have unique data. You need to create a UNIQUE with all the fields that can't be reapeated.

UNIQUE(Model, Serial, Lot, Line)

But if all your fields needs to be unique, i think that your table has no primary key, and you should create a PRIMARY KEY of all fields instead a UNIQUE.