File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Need to add a check constriant to SQL2000 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need to add a check constriant to SQL2000" Watch "Need to add a check constriant to SQL2000" New topic
Author

Need to add a check constriant to SQL2000

M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
M Burke
Ranch Hand

Joined: Jun 25, 2004
Posts: 388
I will give that a try, Paul. Thanks
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Need to add a check constriant to SQL2000