views:

43

answers:

2

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
+4  A: 

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); 
Michael Pakhantsov
can i get the result like i want if i've been use this?
klox
@klox, Yes, UNIQUE(Model, Serial, Lot, Line) mean that combination of fields Model, Serial, Lot, Line must be unique in whole table, in your current design you can't have more than value per table for each column, i.e. can't add two models remocon - because this column must be unique, so remove unique constrain for each column and add unique constraint for table
Michael Pakhantsov
sorry i'm newbie..can you give me sql syntax completely for this case?because i still cant see the different between unique index and unique constraint..thanks
klox
@klox, added table definition
Michael Pakhantsov
how if using existing table?
klox
+1 this awesome..this code helpfull for me..thanks Michael.
klox
A: 

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.

Dubas