Simple constraint question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Simple constraint question

Author

Topic

glt101
Starting Member

28 Posts

Posted - 2007-08-06 : 13:00:40

Hi,

I have a simple question regarding a constraint.
In the following table:

Primary key, Identity, IDRULE, int
IDSN, int
VERSION, nvarchar(5)
EMAILFRAG, ntext
CREATE_DATE, datetime

I want to create a contraint that requires both
the VERSION and EMAILFRAG field to contain text,
if either of those fields contain text.

We are using SQL Server 2000.

Many thanks,
Geoff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 13:05:48

CHECK (datalength(VERSION) > 0 and datalength(EMAILFRAG) > 0)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-06 : 13:10:00

I think you need to add a COALESCE or two incase the values are NULL. Maybe something like:
CHECK (COALESCE(datalength(VERSION), 0) > 0 and COEALESCE(datalength(EMAILFRAG), 0) > 0)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-06 : 13:21:52

[code]
CHECK (
case
when VERSION is null and EMAILFRAG is null
then 1
when datalength(VERSION) > 0 and datalength(EMAILFRAG) > 0
then 1
else 0 end = 1 )
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources