constraints

Hiya peops
im creating a constraint using enterprise manager for 1 of my tables n was wondering how u constructively create a constraint expression, there is a specific style,right??
Thanksscript it out of EM and it'll give you the syntax

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!

constraint expression for unique keys

if i have a table which defines a rule as "combination of two field
must be unique", how can I write this in a constraint expression
section?
i started learning more about ms sql side to handle all the necessary
rules in back-end instead of front-end.
also any good learning links, references, or book recommandations?
thanksan excerpt from BOL:

"C. Using UNIQUE constraints
UNIQUE constraints are used to enforce uniqueness on nonprimary key
columns. The following example enforces a restriction that the Name
column of the Product table must be unique.

Copy Code
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

"|||can you explain what "NONCLUSTERED" is doing there? is that for
non-relation to a field in other table?

Alexander Kuznetsov wrote:
> an excerpt from BOL:
> "C. Using UNIQUE constraints
> UNIQUE constraints are used to enforce uniqueness on nonprimary key
> columns. The following example enforces a restriction that the Name
> column of the Product table must be unique.
> Copy Code
> Name nvarchar(100) NOT NULL
> UNIQUE NONCLUSTERED
> "|||SQL Server implicitly creates an index to implement a uinque
constraint. In this case NONCLUSTERED means the index will be
non-clustered.|||HandersonVA (handersonva@.hotmail.com) writes:
> if i have a table which defines a rule as "combination of two field
> must be unique", how can I write this in a constraint expression
> section?

CONSTRAINT u_tbl UNIQUE (col1, col2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> if i have a table which defines a rule as "combination of two field must be unique", how can I write this in a constraint expression section? <<

CONSTRAINT unique_location UNIQUE (x, y)
>> any good learning links, references, or book recommandations? <<

I recommend buying all of my books :)