Syntax for UNIQUE constraint

[Guinness Mann]

I have an existing table:

CREATE TABLE TestQuestions (
qId int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_TestQuestions PRIMARY KEY NONCLUSTERED,

testId int NOT NULL
REFERENCES Tests(testId)
ON DELETE CASCADE,

objectiveId varchar (30) NULL

qType int NOT NULL
CONSTRAINT DF_TestQuestions_qType DEFAULT (0),
)

And I'd like to add the constraint:

ALTER TABLE TestQuestions ALTER COLUMN objectiveId
CONSTRAINT U_TestQuestions UNIQUE NONCLUSTERED (testId, objectiveId)

Although obviously that's not the correct syntax. Could you tell me
what *is* the correct syntax?

Thanks,

-- Rick

[Dan Guzman]

Try:

ALTER TABLE TestQuestions
ADD CONSTRAINT U_TestQuestions
UNIQUE NONCLUSTERED (testId, objectiveId)

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Guinness Mann" <GM***@dublin.com> wrote in message
news:MP************************@news.newsguy.com.. .

I have an existing table:

CREATE TABLE TestQuestions (
qId int NOT NULL IDENTITY(1,1)
CONSTRAINT PK_TestQuestions PRIMARY KEY NONCLUSTERED,

testId int NOT NULL
REFERENCES Tests(testId)
ON DELETE CASCADE,

objectiveId varchar (30) NULL

qType int NOT NULL
CONSTRAINT DF_TestQuestions_qType DEFAULT (0),
)

And I'd like to add the constraint:

ALTER TABLE TestQuestions ALTER COLUMN objectiveId
CONSTRAINT U_TestQuestions UNIQUE NONCLUSTERED (testId, objectiveId)

Although obviously that's not the correct syntax. Could you tell me
what *is* the correct syntax?

Thanks,

-- Rick