Unique Constraint for 2 columns in SQL 2005

Question

Post reply

Unique Constraint for 2 columns in SQL 2005

msforumpost

Right there with Babe

Points: 795
More actions
March 31, 2009 at 5:13 am

#132316

I have 2 columns of datatype int (integer) in a table. Just like creating a unique constraint on a column , in order to get unique values in the table, can I create unique constraint for those 2 columns, so that no 2 values combined in the 2 columns are the same.
One of these 2 columns is a foreign key column
If you observe now the values for rows 3 and 4 are the same.. Now I want to have unique values for both the columns combined. I am currently using SQL 2005. Is there any solution for this in SQL 2005 ? Something like arriving at GUID from 2 columns ....
Thanks in Advance

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Just as simple as this...

-- Adding constraint to an existing table

ALTER TABLE SomeTable WITH CHECK ADD CONSTRAINT [UK_SomeTable_SomeCompositeKey] UNIQUE( KeyColumn1, KeyColumn2 )

-- Adding constraint when creating new table

CREATE TABLE SomeTable

(

KeyColumn1 INT NOT NULL,

KeyColumn2 INT NOT NULL,

CONSTRAINT [UK_SomeTable_SomeCompositeKey] UNIQUE

(

KeyColumn1,

KeyColumn2

)

Edit:

1. The ADDADD thing is the mis-interpretation of SSC, just use single ADD in the query

2. Added an example of adding constraint in a CREATE statement.

--Ramesh

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

Hi

Ramesh, Instead of Unique Constraint why can't we have Unique Index??

Thanks -- Vijaya Kadiyala

http://www.dotnetvj.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

GSquared SSC Guru Points: 260824 More actions · Answer 3

You can use a unique index. The constraint will actually create a unique index. That's how it works.

I can't tell from the original post if it's included in the sample, but one thing you can run into with a unique constraint/index is that this won't be prevented:

Col1 Col2

1 2

2 1

The only way I know of to prevent that is through code. That means either an insert proc, or a trigger, or something of that sort. Might be other methods, but that's the only one I can think of. (I've tried a few, none worked.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Vijaya Kadiyala (3/31/2009)
Hi
Ramesh, Instead of Unique Constraint why can't we have Unique Index??
Thanks -- Vijaya Kadiyala
http://www.dotnetvj.com

There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Enforcing a UNIQUE constraint will make the business rule/data integrity meaningful and thereby directing the objective of the UNIQUE index it creates.

--Ramesh

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 5

CREATE TABLE [dbo].[t3] (

[n1] [int] not null,

[n2] [int] not null,

[n3] as n1 + n2 ,

[n4] as n1 * n2

CONSTRAINT [t3_unique_key] unique (n3,n4)

)

Create 2 computed columns n3,n4 as shown below. declare unique key constraint on it.

That shd detect the following scenario.

Regards,
Raj

http://Strictlysql.blogspot.com

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 6

One more solution..

Copyrights to my collegue sitting in next desk.. 🙂

create table t3(

n1 int not null,

n2 int not null,

n3 as case when n1< n2

then cast(n1 as varchar)+','+cast(n2 as varchar) else

cast(n2 as varchar)+','+cast(n1 as varchar) end unique

);

insert into t3(n1,n2) values (10,11);

insert into t3(n2,n1) values (11,10);

Regards,
Raj

http://Strictlysql.blogspot.com

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

arr.nagaraj (4/1/2009)
insert into t3(n1,n2) values (10,11);
insert into t3(n2,n1) values (11,10);

These two statements are semantically the same.

You probably wanted to write this instead:

insert into t3(n1,n2) values (10,11);

insert into t3(n1,n2) values (11,10);

Another possible solution to this requirement might be ("might" because I don't know the business problem):

CREATE TABLE dbo.t3 (

n1 int NOT NULL

,n2 int NOT NULL

,CONSTRAINT UQ_t3 UNIQUE (n1, n2)

,CONSTRAINT CK_t3_n1_LE_n2 CHECK (n1<=n2)

);

Best Regards,

Chris Büttner

GSquared SSC Guru Points: 260824 More actions · Answer 8

arr.nagaraj (4/1/2009)
CREATE TABLE [dbo].[t3] (
[n1] [int] not null,
[n2] [int] not null,
[n3] as n1 + n2 ,
[n4] as n1 * n2
CONSTRAINT [t3_unique_key] unique (n3,n4)
)
Create 2 computed columns n3,n4 as shown below. declare unique key constraint on it.
That shd detect the following scenario.

Clever. I thought of each of those separately, but not combined. Should work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 9

Chris,

The buisness requirement is

n1,n2 shd be unique even when interchanged.

unique constraint on 2 columns shd work even when values are interchged.

n1 n2

11 10 ->allowed.

10 11 -> fail as 11,10( 10,11's already exists

your example i am afraid will fail as n1 > n2 at first insert itself, however by req it shd be allowed.

Regards,
Raj

http://Strictlysql.blogspot.com

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 10

Hi

Computed columns is very nice solution for this requirement...thanks nagraj for showing this..

Thanks -- Vijaya Kadiyala

http://www.dotnetvj.vom

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

Hi arr.nagaraj,

why do you think it should be allowed?

If the two columns store the possible combinations of fruit that are sold in a bag, then the order surely doesn't matter and every possible combination can be stored in the table definition I gave.

You are assuming that the order of the two columns does matter to the application, and I am just saying that this might not be the case.

But here I need to ask a question that I should have asked before.

The original poster stated that one of the columns is a foreign key.

Columns n1 and n2 seem to represent the same type of information, therefore I wonder why the other column is not a foreign key as well.

I would really be interested to know what those two columns represent.

Best Regards,

Chris Büttner

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 12

Hi Chris

/*

why do you think it should be allowed?

If the two columns store the possible combinations of fruit that are sold in a bag, then the order surely doesn't matter and every possible combination can be stored in the table definition I gave.

*/

When the table is blank, assume i attempt to insert

iInsert into t3 values(11,10)

By your def it will fail.

Assume, that the application doesnt have any intention of inserting 10,11.

But you are blocking 11,10 right away for no reason. Every possible combo doesnt stored by

your def as it only allows it when 1st is bigger than 2nd. I donno..may be I am missing your inention behind n1 > n2.

I cant guess the reason why orginal poster wanted that..just this one

was a interesting one to solve 🙂

@vijay-2,

wcm.. 🙂

Regards,
Raj

http://Strictlysql.blogspot.com

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 13

Hi arr.nagaraj,

But you are blocking 11,10 right away for no reason.

I told the reason already: If order doesnt matter, then 10,11 means the same as 11,10.

So I allow to store the set {10,11}, but "block" duplicates by allowing only the ordered (ascending) representation of this set to be stored.

Best Regards,

Chris Büttner