Conditional constraints on table columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Conditional constraints on table columns

Author

Topic

negmat
Starting Member

20 Posts

Posted - 2006-11-28 : 10:49:52

Guys,

Does SQL Server allow for creating conditional constraints on the table columns?

My table is:

CREATE TABLE [X]
(

[UID] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[B] [bit] NOT NULL ,

CONSTRAINT [PK_X] PRIMARY KEY CLUSTERED
(

[UID]

) ON [PRIMARY]

) ON [PRIMARY]

I want to add the following constraints:

CONSTRAINT [IX_UNIQUE1] UNIQUE NONCLUSTERED

(
A, B = 1

) ON [PRIMARY]

CONSTRAINT [IX_UNIQUE2] UNIQUE NONCLUSTERED

(
UPPER(A), B = 0

) ON [PRIMARY]

That is, if the table contains "Hello" in column A, then an insert of ("hello", 1) will be rejected, but ("hello", 0) will be inserted.

Any ideas?

Thanks a lot

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-28 : 10:59:19

I think what you want is a check constraint and a seperate unique constraint.

The unique constraint on A, B
The check constraint would implement you capitolization business rule.

Jay White

negmat
Starting Member

20 Posts

Posted - 2006-11-30 : 10:35:15

I tried to use check constraint, but it appeared to be inefficient to solve the problem.

I wound up using an INSERT TRIGGER. It got a bit messy, but solved the problem.

Using a check constraint is probably more efficient, but I'm not sure whether it is possible in my case.

Thanks a lot

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-30 : 10:53:41

What about this?

CREATE TABLE [X] 
(
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[B] [bit] NOT NULL,
Check (lower(a) = a and b = 0 or a = upper(a) and b = 1)
)

Harsh Athalye
India.
"Nothing is Impossible"

negmat
Starting Member

20 Posts

Posted - 2006-11-30 : 11:20:56

It is possible that I do not understand how CHECK Constraint works.

Please correct me if I am wrong, but I believe that the check, as shown above, will work as such:

If B = 0, only allow strings which are all lower case, to be inserted.
If B = 1, only allow strings which are all upper case, to be inserted.

This is not what I am looking for.

I need the decision to be made based on what is already in the table. This is exactly why I don't see a trivial way of using the Check constraint. It is possible that I could write a stored procedure which would go through a table and compare the values to the ones being inserted and then call this stored procedure in a CHECK constraint ... but that would be same as just having a trigger.

Am I confused?

Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources