Check Constraint Fails during update : 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)


-- Anonymous, September 15, 2000



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,


-- Anonymous, September 22, 2000

Moderation questions? read the FAQ