July 7, 2003 at 12:31 pm
Is it possible in SQL server 2000 to create a check contraint on a column that checks the value of another column for the row? Specifically, I want to create a check contraint on column B that will only allow column B to be NULL if Column A is also NULL.
Thanks
Tim
July 7, 2003 at 12:38 pm
There is probably a better way but the following works...
USE TEMPDB
BEGIN TRAN
GO
CREATE TABLE MY_TABLE(
THE_VALUE_A CHAR(1),
THE_VALUE_B CHAR(1),
CONSTRAINT CHK_A_AND_B CHECK(
(THE_VALUE_A IS NULL AND THE_VALUE_B IS NULL)
OR
(THE_VALUE_A IS NOT NULL AND THE_VALUE_B IS NULL)
OR
(THE_VALUE_A IS NOT NULL AND THE_VALUE_B IS NOT NULL)
)
);
GO
/* insert ok */
INSERT INTO MY_TABLE VALUES('A','B');
GO
/* insert ok */
INSERT INTO MY_TABLE VALUES('A',NULL);
GO
/* insert not ok */
INSERT INTO MY_TABLE VALUES(NULL,'B');
GO
/* insert ok */
INSERT INTO MY_TABLE VALUES(NULL,NULL);
GO
ROLLBACK;
Billy
July 8, 2003 at 6:46 am
Thanks - works fine. For some reason I thought it should be more complex!
Tim
July 8, 2003 at 6:57 am
This will work a little better:
USE TEMPDB
BEGIN TRAN
GO
CREATE TABLE MY_TABLE(
THE_VALUE_A CHAR(1),
THE_VALUE_B CHAR(1),
CONSTRAINT CHK_A_AND_B
CHECK (NOT(THE_VALUE_A IS NOT NULL AND THE_VALUE_B IS NULL)))
GO
/* insert ok */
INSERT INTO MY_TABLE VALUES('A','B');
GO
/* insert not ok */
INSERT INTO MY_TABLE VALUES('A',NULL);
GO
/* insert ok */
INSERT INTO MY_TABLE VALUES(NULL,'B');
GO
/* insert ok */
INSERT INTO MY_TABLE VALUES(NULL,NULL);
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply