April 17, 2012 at 2:48 pm
I have a table with 3 columns
A int Null
B int Null
C int Not Null -- Cloumn 'C' can have only 2 values 150 & 151
I need to create a Check Constraint with the conditions
if value in Column 'C' is 150 then Column 'A' should have a value for sure (ie IS Not Null)
if value in Column 'C' is 151 then Column 'B' should have a value for sure (ie IS Not Null)
Can any one help me how to create this perticular check constraint, and if needed a function also.
April 17, 2012 at 3:15 pm
Edit: one check constraint is enough...
CHECK ((C = 150 AND A IS NOT NULL) OR (C = 151 AND B IS NOT NULL))
Not syntax checked, but should get you on the right path at least.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 3:16 pm
USE test
CREATE TABLE testConstraint (
A int Null,
B int Null,
C int Not Null)
alter table testConstraint
add constraint threeColumn CHECK ((C = 150 AND A IS NOT NULL) OR (C = 151 AND B IS NOT NULL))
--fail
INSERT INTO testConstraint (A, B, C)
VALUES (NULL, NULL, 150)
--fail
INSERT INTO testConstraint (A, B, C)
VALUES (NULL, NULL, 151)
--success
INSERT INTO testConstraint (A, B, C)
VALUES (21, NULL, 150)
--success
INSERT INTO testConstraint (A, B, C)
VALUES (NULL, 200, 151)
--success
INSERT INTO testConstraint (A, B, C)
VALUES (500, 600, 150)
--success
INSERT INTO testConstraint (A, B, C)
VALUES (7000, 9000, 151)
--fail
INSERT INTO testConstraint (A, B, C)
VALUES (500, 600, 156)
--fail
INSERT INTO testConstraint (A, B, C)
VALUES (NULL, NULL, 156)
Tested... Gail, any reason NOT to do 1 constraint from your point of view?
Jared
CE - Microsoft
April 17, 2012 at 3:20 pm
Something like this?
USE tempdb
GO
CREATE TABLE dbo.three_columns
(
A INT NULL,
B INT NULL,
C INT NOT NULL
);
GO
ALTER TABLE dbo.three_columns ADD CONSTRAINT [ck_dbo.three_columns.C] CHECK
(
(C = 150 AND A IS NOT NULL)
OR
(C = 151 AND B IS NOT NULL)
);
GO
Now that we have that out of the way...what does B mean when C = 150? what does A mean when C = 151? I guess I am wondering if A and B can collapsed into one column.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 17, 2012 at 3:40 pm
Thanks you very much,
Is there any way
if the Column 'C' is allowed to have other values than 150 & 151
In That perticular senario both columns A & B can accept nulls.
How could I change this check constraint.
April 17, 2012 at 3:47 pm
CREATE TABLE dbo.three_columns
(
A INT NULL,
B INT NULL,
C INT NOT NULL
);
GO
ALTER TABLE dbo.three_columns ADD CONSTRAINT [ck_dbo.three_columns.C] CHECK
(
(C < 150 OR C > 151) OR
(C = 150 AND A IS NOT NULL)
OR
(C = 151 AND B IS NOT NULL)
);
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply