Not allow to enter dulicate value on three column combination
Dear Experts,
I have a table namely Tankers with the primary key TransNo (Numeric,Identity yes 1),
Sometimes users update one tanker twice on same loading date and loading point. To avoid such mistake, I have created index with type Unique Key screen shot attached. But it is not working and accepting duplicate value against same truck on same loading date from the same loading point. Please help how could we fix this?
Rgds.
Mehram
screenshot.jpg
I have a table namely Tankers with the primary key TransNo (Numeric,Identity yes 1),
Sometimes users update one tanker twice on same loading date and loading point. To avoid such mistake, I have created index with type Unique Key screen shot attached. But it is not working and accepting duplicate value against same truck on same loading date from the same loading point. Please help how could we fix this?
Rgds.
Mehram
screenshot.jpg
There is one option while creating INDEX named IGNORE_DUP_KEY, you would be able to find it in the snapshot attached by moving down..
Kindly set it to OFF to prevent duplicate values for these three columns in that table.
Kindly run the query given above to find existing duplicate records if any..
Kindly set it to OFF to prevent duplicate values for these three columns in that table.
Kindly run the query given above to find existing duplicate records if any..
ASKER
HainKurt: Result of below query is zero record.
select trailerno, loadon, lb
from tankers
group by trailerno, loadon, lb
having count(1)>1
rrjegan17: There is not snapshot
Kindly set it to OFF to prevent duplicate values for these three columns in that table.
Could you guide me how to do this?
Kindly run the query given above to find existing duplicate records if any..
result Zero
select trailerno, loadon, lb
from tankers
group by trailerno, loadon, lb
having count(1)>1
rrjegan17: There is not snapshot
Kindly set it to OFF to prevent duplicate values for these three columns in that table.
Could you guide me how to do this?
Kindly run the query given above to find existing duplicate records if any..
result Zero
Since it returned zero records, now you can drop your existing unique index and create this one
CREATE UNIQUE INDEX ON ur_table_name(trailerno, loadon, lb);
Kindly replace your column names if anything is wrong..
And hope you have a primary key defined on TransNo column..
CREATE UNIQUE INDEX ON ur_table_name(trailerno, loadon, lb);
Kindly replace your column names if anything is wrong..
And hope you have a primary key defined on TransNo column..
ASKER
Primary Key is TransNo
Column Names are also correct.
CREATE UNIQUE INDEX ON Tankers(trailerno, loadon, lb);
Error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.
Column Names are also correct.
CREATE UNIQUE INDEX ON Tankers(trailerno, loadon, lb);
Error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sir, still accepting dublicate values.
I am pasting below insert statement, through which I get data to the excel file and insert into the real table. May be something you found below:
Declare @Path Varchar(50)
Set @PAth='C:\DataInsertFile.x
Begin
IF object_id('Tempp') IS NOT NULL DROP TABLE Tempp
EXEC('SELECT * Into Tempp FROM OPENDATASOURCE(''Microsoft
Insert Into Tankers(ComCode,FYear, SU,PermitNo,Receiver,Produ
qty,qty15c, loadon,tripno,docno,sealno
select CC='0'+ convert(varchar(1), a.CC), J.WorkingYEar, b.transno,a.permitno,i.tra
a.invoiceno,a.RefinvNo,a.D
a.driverName,a.IDCardNo,A.
from Tempp a
join mtSup b on a.supplier=b.su
Join mtPrd c on a.product=c.product
Join mtTrp d on a.transporter=d.Trp
Join mtLP e on a.lp=loadingpoint
Join mtVia f on a.via=f.via
Join mtDP g on a.dp=g.dischargingpoint
Join mtLB h on a.LC=h.Loadby
Join mtCnCode i on a.Receivers=i.receiver
Join mtYear j on a.despdate >= j.Startingdate and a.despdate <=j.EndingDate
End
I am pasting below insert statement, through which I get data to the excel file and insert into the real table. May be something you found below:
Declare @Path Varchar(50)
Set @PAth='C:\DataInsertFile.x
Begin
IF object_id('Tempp') IS NOT NULL DROP TABLE Tempp
EXEC('SELECT * Into Tempp FROM OPENDATASOURCE(''Microsoft
Insert Into Tankers(ComCode,FYear, SU,PermitNo,Receiver,Produ
qty,qty15c, loadon,tripno,docno,sealno
select CC='0'+ convert(varchar(1), a.CC), J.WorkingYEar, b.transno,a.permitno,i.tra
a.invoiceno,a.RefinvNo,a.D
a.driverName,a.IDCardNo,A.
from Tempp a
join mtSup b on a.supplier=b.su
Join mtPrd c on a.product=c.product
Join mtTrp d on a.transporter=d.Trp
Join mtLP e on a.lp=loadingpoint
Join mtVia f on a.via=f.via
Join mtDP g on a.dp=g.dischargingpoint
Join mtLB h on a.LC=h.Loadby
Join mtCnCode i on a.Receivers=i.receiver
Join mtYear j on a.despdate >= j.Startingdate and a.despdate <=j.EndingDate
End
Unique index will not allow Duplicate values.
Kindly post some records which you mentioned as duplicates for the columns trailerno, loadon, lb
Kindly post some records which you mentioned as duplicates for the columns trailerno, loadon, lb
ASKER
Sir, for example,
User just updated fourteen records into the real table from the file which has already existing in C:
The second users click the same button by mistake then the table accepted the all fourteen rows once again because there is no check as yet.
Data of first fourteen rows provided below:
LSC 4094 22-Dec-09 5
LSC 951 22-Dec-09 5
P 2463 22-Dec-09 1
TLA 380 22-Dec-09 1
TLA 717 22-Dec-09 5
TLF 545 22-Dec-09 5
TLG 810 22-Dec-09 5
TLJ 328 22-Dec-09 5
TLN 028 22-Dec-09 5
TLP 439 22-Dec-09 5
TLQ 331 22-Dec-09 5
TLQ 431 22-Dec-09 5
Y 0082 22-Dec-09 5
Z 2159 22-Dec-09 1
Please help.
Rgds.
Mehram
User just updated fourteen records into the real table from the file which has already existing in C:
The second users click the same button by mistake then the table accepted the all fourteen rows once again because there is no check as yet.
Data of first fourteen rows provided below:
LSC 4094 22-Dec-09 5
LSC 951 22-Dec-09 5
P 2463 22-Dec-09 1
TLA 380 22-Dec-09 1
TLA 717 22-Dec-09 5
TLF 545 22-Dec-09 5
TLG 810 22-Dec-09 5
TLJ 328 22-Dec-09 5
TLN 028 22-Dec-09 5
TLP 439 22-Dec-09 5
TLQ 331 22-Dec-09 5
TLQ 431 22-Dec-09 5
Y 0082 22-Dec-09 5
Z 2159 22-Dec-09 1
Please help.
Rgds.
Mehram
Got it..
The reason why it allows again is that your loadon column stored time values in it which makes it unique ( and hence not a duplicate). loadon will differ for fourteen records for user1 compared to user2
Workaround:
1. You need to validate in your application to get it work.
2. Else add one more column to indicate the loaded status so that second load can be avoided..
The reason why it allows again is that your loadon column stored time values in it which makes it unique ( and hence not a duplicate). loadon will differ for fourteen records for user1 compared to user2
Workaround:
1. You need to validate in your application to get it work.
2. Else add one more column to indicate the loaded status so that second load can be avoided..
ASKER
This was correct, next question asked by me due to some error in my excel file, which I just identified. Tks again.
if it is allowing, unistall/reinstall db server, kidding ;)
run this
select trailerno, loadon, lb
from tankers
group by trailerno, loadon, lb
having count(1)>1
to see if there is such records now...