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
MehramAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Oops.. Missed index name
Try this one

CREATE UNIQUE INDEX IDX_Tankers_unq ON Tankers(trailerno, loadon, lb);
0
 
HainKurtSr. System AnalystCommented:
if there is a unique index it should not allow

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...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
MehramAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
MehramAuthor Commented:
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'.
0
 
MehramAuthor Commented:
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.xls'

Begin
IF object_id('Tempp') IS NOT NULL DROP TABLE Tempp
EXEC('SELECT * Into Tempp FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source=' + @Path + '; Extended Properties=''''Excel 12.0'''''')...[Sheet1$]')
Insert Into Tankers(ComCode,FYear, SU,PermitNo,Receiver,Product,unitvalue,ExchangeRate,LB,LP,via,LF,DP,Trp,invoiceno, SupInvoiceNo,DD250No,orderno,trailerno,
qty,qty15c, loadon,tripno,docno,sealno,drivername,idcard,licenseno,location)

select      CC='0'+ convert(varchar(1), a.CC), J.WorkingYEar, b.transno,a.permitno,i.transno,c.transno,a.perunit,a.Er,h.transno,e.transno,f.transno,g.transno,g.transno,d.transno,
            a.invoiceno,a.RefinvNo,a.DD250No,a.OrderNo,a.TLNo,a.DespQty,A.Qtyat15c,a.despdate,a.tripno,a.docno,a.sealno,
            a.driverName,a.IDCardNo,A.LicenseNo,a.Location
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Unique index will not allow Duplicate values.
Kindly post some records which you mentioned as duplicates for the columns trailerno, loadon, lb
0
 
MehramAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
MehramAuthor Commented:
This was correct, next question asked by me due to some error in my excel file, which I just identified. Tks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.