The INSERT statement conflicted with the CHECK constraint "CK_tbl_UserTargetLevels". The conflict occurred in database "myDB", table "dbo.tbl_UserTargetLevels".
[code]

I can't see what's causing this, although it must be something really simple because I've been staring at it far too long.  Please can anyone spot the problem with the insert?  Here's the full code:

[code]
CREATE TABLE tbl_UserTargetLevels
	(
	[ID] INT IDENTITY(1,1) PRIMARY KEY,
	userID int NOT NULL,
	sgsID int NOT NULL,
	targetLevel numeric(5, 2) NULL,
	notApplicable bit NULL
	)
GO

-- Set default bit column value to 0
ALTER TABLE tbl_UserTargetLevels ADD CONSTRAINT DF_tbl_UserTargetLevels_notApplicable DEFAULT 0 FOR notApplicable
GO

-- Ensure that targetLevel and notApplicable are not both NULL
ALTER TABLE tbl_UserTargetLevels ADD CONSTRAINT	CK_tbl_UserTargetLevels CHECK (notApplicable IS NULL AND targetLevel IS NULL)
GO

INSERT INTO tbl_UserTargetLevels (userID, sgsID, targetLevel, notApplicable) VALUES (1, 1, NULL, 1) -- ERROR!!
GO

quote:

---

Originally posted by TG

Your CHECK "...And targetLevel IS NULL" is being violated by your insert. You are passing "1" which is obviously not null

EDIT:
Odd - why create columns that you want to enforces are always NULL ???

Be One with the Optimizer
TG

---

ALTER TABLE tbl_UserTargetLevels ADD CONSTRAINT	CK_tbl_UserTargetLevels CHECK (notApplicable IS NOT NULL AND targetLevel IS NOT NULL)

CHECK (isnull(notApplicable, 1) + isnull(targetLevel, 1) < 2)

CHECK ((notApplicable is not null) or (targetLevel is not null))

CHECK ((notApplicable is null) ^ (targetLevel is null))

quote:

---

in which case it actually could have been one column

---

CHECK ((isnull(notApplicable, 1) + isnull(targetLevel, 1) <> 2) AND (isnull(notApplicable, 0) + isnull(targetLevel, 0) <> 0))

quote:

---

Originally posted by russell

if one must be null and the other not null (in which case it actually could have been one column) can do this

CHECK ((notApplicable is null) ^ (targetLevel is null))

---

use tempdb
go
CREATE TABLE tbl_UserTargetLevels
	(
	[ID] INT IDENTITY(1,1) PRIMARY KEY,
	userID int NOT NULL,
	sgsID int NOT NULL,
	targetLevel numeric(5, 2) NULL,
	notApplicable bit NULL
	)
GO

-- Set default bit column value to 0
ALTER TABLE tbl_UserTargetLevels ADD CONSTRAINT DF_tbl_UserTargetLevels_notApplicable DEFAULT 0 FOR notApplicable
GO

-- Ensure that targetLevel and notApplicable are not both NULL
ALTER TABLE tbl_UserTargetLevels ADD CONSTRAINT	CK_tbl_UserTargetLevels  
    CHECK (coalesce(targetLevel, notapplicable) is not null and targetLevel + notapplicable is null) 
GO

INSERT INTO tbl_UserTargetLevels (userID, sgsID, targetLevel, notApplicable) VALUES (1, 1, NULL, 1) 
INSERT INTO tbl_UserTargetLevels (userID, sgsID, targetLevel, notApplicable) VALUES (1, 1, 1.2, null) 
INSERT INTO tbl_UserTargetLevels (userID, sgsID, targetLevel, notApplicable) VALUES (1, 1, null, null) 
INSERT INTO tbl_UserTargetLevels (userID, sgsID, targetLevel, notApplicable) VALUES (1, 1, 2.1, 0) 

select * from tbl_UserTargetLevels

GO
drop table tbl_UserTargetLevels

OUTPUT:
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "CK_tbl_UserTargetLevels". The conflict occurred in database "tempdb", table "dbo.tbl_UserTargetLevels".
The statement has been terminated.
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint "CK_tbl_UserTargetLevels". The conflict occurred in database "tempdb", table "dbo.tbl_UserTargetLevels".
The statement has been terminated.
ID          userID      sgsID       targetLevel                             notApplicable
----------- ----------- ----------- --------------------------------------- -------------
1           1           1           NULL                                    1
2           1           1           1.20                                    NULL

quote:

---

So you are trying to inforce "mutual exclusivity" where exactly one value needs to be null while the other is not null.

---

CHECK (coalesce(targetLevel, notapplicable) is not null and targetLevel + notapplicable is null)

ALTER TABLE dbo.tbl_UserTargetLevels ADD CONSTRAINT CK_tbl_UserTargetLevels CHECK (coalesce(targetLevel, notapplicable) is not null and targetLevel + notapplicable is null) 

quote:

---

Originally posted by TG

I think the error is probably because you are trying to add the new version version of the check constraint without dropping the current one first.

As to your first question, any expression using a NULL will resolve to NULL. So if either (targetLevel or notapplicable) is null when you attempt to ADD them the result must be NULL. So that half of the check inures that at lease one of the values is NULL.

Be One with the Optimizer
TG

---