Add constraint

Question

Post reply

Add constraint

Pyman

SSChampion

Points: 13830
More actions
March 15, 2012 at 6:24 am

#258674

Hi Everyone,
Hope all is well.
I have a question on TSQL. Can we add a constraint on a combination of multiple columns?
IN a table if column1 has primary key and column2 has foreign key. Then can I add a check constraint or unique constraint on the combination of column1 and column2?
ALTER TABLE dbo.TableName ADD CONSTRAINT abc UNIQUE (column1+column2);
Or is it mandatory for a third column exist in the table with a combination of column 1 and column2?
Thanks for your help
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 11 posts - 1 through 10 (of 10 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

You can put a constraint on multiple columns.

However, the syntax for creating a UNIQUE constraint is as follows:

CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[myTable]

(

[myColumn1] ASC,

[myColumn2] ASC

) ON [PRIMARY]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Pyman SSChampion Points: 13830 More actions · Answer 2

Thanks for the reply. This a requirement from one of my developers. So in this case I need to have a third column that is a combination of column1 and column2 and then I should create a constraint on it. Agree?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No, not at all.

Koen showed you how to create a unique index on multiple columns. You can do a unique constraint the same way, just specify all the columns within the constraint definition.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Koen Verbeeck (3/15/2012)
However, the syntax for creating a UNIQUE constraint is as follows:

That's the syntax for creating a unique index, not a unique constraint.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

GilaMonster (3/15/2012)
Koen Verbeeck (3/15/2012)
However, the syntax for creating a UNIQUE constraint is as follows:
That's the syntax for creating a unique index, not a unique constraint.

You're right. Usually I use the terms interchangeably, because there's no actual difference between the unique constraint and the unique index since SQL Server 2005. (correct me if I'm mistaken)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Koen Verbeeck (3/15/2012)
Usually I use the terms interchangeably, because there's no actual difference between the unique constraint and the unique index since SQL Server 2005. (correct me if I'm mistaken)

There are differences...

Go and create 2 tables and then on each create a unique index called uq_Testing. Then go and add a unique constraint on each called uq_Testing2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 7

If you really need the sum of both numbers to be unique, rather than a unique combination of the two columns, then yes, you would need to create a 3rd computed column, then create a unique constraint (or index) on this:

CREATE TABLE #test (col1 INT, col2 INT, col3 AS col1+col2)

ALTER TABLE #test ADD CONSTRAINT abc UNIQUE (col3)

INSERT INTO #test

SELECT 1,2 --succeeds

INSERT INTO #test

SELECT 2,1 --fails with unique constraint violation

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

GilaMonster (3/15/2012)
Koen Verbeeck (3/15/2012)
Usually I use the terms interchangeably, because there's no actual difference between the unique constraint and the unique index since SQL Server 2005. (correct me if I'm mistaken)
There are differences...
Go and create 2 tables and then on each create a unique index called uq_Testing. Then go and add a unique constraint on each called uq_Testing2

Well dammit.

I have to trust the Internet less when I'm doing research 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Koen Verbeeck (3/15/2012)
GilaMonster (3/15/2012)
Koen Verbeeck (3/15/2012)
Usually I use the terms interchangeably, because there's no actual difference between the unique constraint and the unique index since SQL Server 2005. (correct me if I'm mistaken)
There are differences...
Go and create 2 tables and then on each create a unique index called uq_Testing. Then go and add a unique constraint on each called uq_Testing2
Well dammit.
I have to trust the Internet less when I'm doing research 🙂

There's 2 more differences I can think of offhand. I'll leave you to research them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pyman SSChampion Points: 13830 More actions · Answer 10

I was able to create the unique constraints.

Thanks.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams