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]