January 8, 2007 at 1:10 am
Hello Everyone,
I have a table with say 4 columns.
A B C D
I want to insert a row in this table in such a way that the combination of values in column A B and C should be unique.
For Example: values for A B C D should accept
1 2 3 4
1 1 2 5
but it should not accept 1 2 3 6 again since 1 2 3 combination for A B C already exist.
How do i approach this.
Please help me
January 8, 2007 at 2:39 am
Create composite unique index on columns A,B,C (not necessarily in this order - use the most selective column as first).
When inserting, check whether a row with the same values exists or not. You can use either WHERE NOT EXISTS, or a LEFT JOIN to the table you are inserting into with accompanying WHERE ... IS NULL. I prefer the variant with IS NULL. (If you don't check anything, insert will fail if it would result in a duplicate).
INSERT INTO my_table (a,b,c,d)
SELECT col1, col2, col3, col4
FROM other_table o
LEFT JOIN my_table mt ON o.col1=mt.a AND o.col2=mt.b AND o.col3=mt.c
WHERE mt.checkcol IS NULL
"checkcol" can be any column that does not allow NULLs; if for example my_table has IDENTITY column, this would be a good candidate.
And this is the other possibility, handy if you insert values of variables - but usable with columns (as above) as well.
INSERT INTO my_table (a,b,c,d)
WHERE NOT EXISTS (select * from my_table where a = @a and b = @b-2 and c = @c)
January 8, 2007 at 2:46 am
Hi,
U can use Indexes for that. Go to manage indexes & create new index using column a,b, c & click on "unique values check box".
u will get ur desired result
January 8, 2007 at 11:23 pm
Thank you Vladan , Anurag.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply