Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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
0
Mehram
Asked:
Mehram
  • 5
  • 5
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now