Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need to add a check constriant to SQL2000

 
M Burke
Ranch Hand
Posts: 401
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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
Ranch Hand
Posts: 401
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I will give that a try, Paul. Thanks
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic