How to get a 2 column constraint in table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get a 2 column constraint in table variable

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-08-04 : 05:11:56

Given the following table variable...


DECLARE @tbl TABLE 
  (
  aID int, 
  bID int
  )

...is it possible to specify in the declaration a unique constraint across both columns? I need to prevent duplicate rows of the same value being inserted, so for example:


INSERT INTO @tbl (aID, bID) VALUES (15, 20)
INSERT INTO @tbl (aID, bID) VALUES (30, 40)
INSERT INTO @tbl (aID, bID) VALUES (65, 99)
INSERT INTO @tbl (aID, bID) VALUES (30, 40)  -- fails here because these two values already exist from the second insert

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-08-04 : 06:46:16

Try this:

DECLARE @tbl TABLE
(
aID int,
bID int,
UNIQUE (aID,bID)
)

--------------------
Rock n Roll with SQL

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-08-04 : 07:07:34

That's got it. Thank you very much.

That was so simple I can't believe I didn't figure it out...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources