Constraint expression not working

Hi!
I have a table with users in which I would like to have a constraint making
sure that for active users (IsActive=1) a valid user name always exists
(OSUserName <> '' AND OSUserName IS NOT NULL).
I enter the following constraint expression in the table designer:
(([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
But when I look at what gets saved, the paranthesis separating the two AND
clauses are gone:
([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
[OSUserName] is null)
And this also make my constraint fail and allowing IsActive = 0 and
OSUserName = 'XYZ'. How can I write the expression to get the desired
effect?
Brgds
JonasYou can write the same expression in the following way:
SIGN(LEN(ISNULL([OSUserName], ''))) = [IsActive]
But I don't understand your logic. Do you really mean that if you change a
user from being active to non-active that you have set the OSUserName to
NULL as well? That is what you constraint enforces now. If you don't need
that, you can simplify your check to LEN(ISNULL([OSUserName], '')) > 0 OR
[IsActive] = 0. I.e. you either have to provide a username or the user must
be inactive.
Jacco Schalkwijk
SQL Server MVP
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
> making sure that for active users (IsActive=1) a valid user name always
> exists (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Instead of using query designer, do your work from Query Analyzer...
ALTER TABLE YourTable
ADD CONSTRAINT CK_IsActive_UserName CHECK
((([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
making
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Jonas,
Create the constraint from QA.
Example:
use northwind
go
create table t (
IsActive smallint not null check (IsActive = 0 or IsActive = 1) default (0),
OSUserName varchar(50) null,
)
go
alter table t
add constraint chk_OSUserName check (([IsActive] = 1 and
isnull([OSUserName], '') > '') or ([IsActive] = 0))
go
insert into t default values
insert into t values(1, 'MSSQLSERVER')
go
insert into t values(1, null)
go
update t
set IsActive = 1
where OSUserName is null
go
select * from t
go
drop table t
go
AMB
"Jonas" wrote:

> Hi!
> I have a table with users in which I would like to have a constraint makin
g
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||No you are not crazy: SQL Server does re-write the check constraint
source.
If a check is defined as "Column in ('a', 'b' )" it is re-written to
"[Column] = 'a' or [Column] = 'b'"
For complex nested conditions, the re-write includes awareness of the
precedence of NOT, AND and OR as defined in Books On Line.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!