How do you find if a unique pair value already exists in a sql table?

Question

Possible Duplicate:
Finding duplicate values in a SQL table

I'm completely new to T-Sql and Sql programming in general so I'm hoping someone can steer me in the right direction. Here's my problem.. I have a table with only 2 columns, AppliedBandwidthSourceKey and AppliedBandwithSource for simplicity I'll call them columns A and B respectively.

Columns A and B compose the primary key. When I try to insert a record where the value for column A already exists I immediately get a Primary Key constraint violation and if column A doesn't exist but column B does I get Unique Key constraint violation. My question is how do I check if the 'value pair' already exists in the table? if does then do nothing otherwise insert.

I've seen several solutions to similar problems using tsql's merge and if not exists statements but I just can't grasp the concept. Any help would be greatly appreciated.

If columns A and B compose the primary key and you are getting a unique key constraint violation, then there must be a unique key on column B. A primary key must be unique, but you can have the same value in column A or B as long as you don't have the a value in both column A and B which already exists in the table. I'd check the indexes on the table - I can't think of a good reason to have a PK on two columns and also have a unique constraint on one of them..! Or maybe you just got mixed up with PK and index (or maybe the DBA has gone crazy!) — Charleh, Jul 10 '12 at 15:38
I may have misstated the problem. Looks like both fields should be individually unique and I got mixed up with the PK and index. — so cal cheesehead, Jul 10 '12 at 16:36
In that case it sounds like you need a unique value for **BOTH** the PK and the unique key - since you get a PK violation I'd say the PK is column A and the unique key is column B - but you say you only want to find out if a particular combination exists...bit confusing this one! — Charleh, Jul 10 '12 at 16:52
*‘Columns A and B compose the primary key. When I try to insert a record where the value for column A already exists I immediately get a Primary Key constraint violation and if column A doesn't exist but column B does I get Unique Key constraint violation.’* – seems like you've got two separate unique constraints on these two columns. IOW, you've got *two (single-column) keys* rather than *one composite key*. Not unusual (for instance, you could have a numeric key for references and a unique constraint on another column, like Name), but the question is whether that is what you really wanted. — Andriy M, Jul 10 '12 at 17:12
@Charleh yes i'm confused myself! my apologies. Your assessment was correct, the PK is column A and the unique key is column B. What I 'think' i need to do is first find if column A is already in the table and secondly if the value for row B is already in the table. So individually look for each value and handle it perhaps with a try/catch block. Does that sound about right? — so cal cheesehead, Jul 10 '12 at 19:05
You can do both with one exists query like 03usr answered, our use a try catch, but it's such a simple query personally I'd go with 03usrs answer — Charleh, Jul 10 '12 at 21:15

score 1 · Answer 1 · answered Jul 10 '12 at 15:33

1

You can do this:

IF NOT EXISTS (select * from yourtable where yourfield1 = 'field1' and yourfield2 = 'field2')
BEGIN
    INSERT INTO ...
END

This simply insert data into yourtable if the data is not found.

answered Jul 10 '12 at 15:33

03Usr

3,335
6
37
63

score 1 · Accepted Answer · answered Jul 10 '12 at 15:38

1

You don't actually have to do the work first . . . after all, that's what the constraints are doing.

Instead, learn about try/catch blocks:

begin try
    insert into t(a, b) values('a', 'b')
end try
begin catch
    print 'Oops! There was a problem, maybe a constraint violation for example'
end catch;

This statement attempts the insert. If there is a failure, then it goes to the "catch" portion, and you can do whatever you want (including ignoring the problem). The documentation is reasonably clear (http://msdn.microsoft.com/en-us/library/ms175976.aspx).

answered Jul 10 '12 at 15:38

Gordon Linoff

1,242,037
58
646
786

what if the insert fails because of another problem e.g. database is down ;) – 03Usr Jul 10 '12 at 15:39
You can check for all the conditions you like, by looking at the error message and error codes. – Gordon Linoff Jul 10 '12 at 15:41

score 0 · Answer 3 · answered Jul 10 '12 at 15:34

0

use a count statment where (A and B) If count returns 0, then the pair doesn't exist. If it returns a number not 0 (X), then the entry pair exists (X) times.

answered Jul 10 '12 at 15:34

Matt Westlake

3,499
7
39
80

Void Ray · Answer 4 · 2012-07-10T16:45:22.843

In some cases you can use left/right join, where you join on your PK's:

insert into tableA (Id,AnotherId)
select b.Id, b.AnotherId
from tableB b
left join tableA a
    on b.Id = a.id
        and b.AnotherId = a.AnotherId
where a.Id is null

Another left join version:

insert into tableA (Id,AnotherId)
select b.Id, b.AnotherId
from (select Id = 5, AnotherId = 5) b
left join tableA a
    on b.Id = a.id
        and b.AnotherId = a.AnotherId
where a.Id is null
and a.Id is null

How do you find if a unique pair value already exists in a sql table?

4 Answers4