Check Constraint Fails during updategreenspun.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)
-- 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