Need to add a check constriant to SQL2000

[M Burke]

I have the following table definitions. Message.fkDiscussionID is a foreign key linked to Discussion.pkDiscussionID. fkDiscussionID is not a unique value, so many rows can have it set to the same value.

I want to do is add a contraint that says, "if fkDiscussionID of a new or existing updated row is equal to any existing row's fkDiscussionID, that only one row in the group has btTopLevel set to 1"

Is this possible and what would be the syntax? Thanks

CREATE TABLE [dbo].[Discussion] (
[pkDiscussionID] [int] IDENTITY (1, 1) NOT NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[Message] (
[pkMessageID] [int] IDENTITY (1, 1) NOT NULL ,
[btTopLevel] [bit] NOT NULL ,
[fkDiscussionID] [int] NOT NULL ,
) ON [PRIMARY]

[Paul Sturrock]

So you need a unique constraint on message (fkDiscussionID, btTopLevel) I think? The easiest way to do this is to use the GUI supplied with SQL Server.

If you can't, I the syntax is somehtign like:

create unique nonclustered index [indexName] on [message] (
   fkDiscussionID,
   btTopLevel
) 

Caveat: I tend to work in SQL Server 2005. I can't remember if the syntax has changed radically between versions. But hopefully you get the idea.

[M Burke]

I will give that a try, Paul. Thanks