Check Constraint Fails during update

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Eric, I have a check constraint on a table which checks whether the userid or usergroupid is not null when password is inserted/updated.It works fine when inserting the data, but while updating it fails. Can anyone help me.

The DDL is :

PwdId int identity(1,1) not null, Password varchar(20) not null, Userid varchar(100) null, UserGroupId varchar(100) null

The check constraint is :

(case when Password is not null and (Userid is null and UserGroupId is null) then 0 else 1 end=1)

Thx..Jay

-- Anonymous, September 15, 2000

Answers

Jay,

The constraint works for me. That is, I receive a constraint violation when I attempt to update a row with a null Userid and null UserGroupID.

The thing I noticed, and that I think you are seeing, is that if you use the Enterprise Manager's "Open Table/Return all rows" window to update a table row, you must type a Control+0 to put in a null value. If you just erase the value, you have an empty string rather than a null!

To double check, use a Transact SQL update statement to test the constraint.

Hope this helps,

Eric

-- Anonymous, September 22, 2000


Moderation questions? read the FAQ