CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlconstraints
KM.
KM.

Reputation: 103587

Simple check constraint not showing up properly in INFORMATION_SCHEMA.CHECK_CONSTRAINTS

I have a fairly simple check constraint, where I want to force two columns to be either both NULL or both NOT NULL. However INFORMATION_SCHEMA.CHECK_CONSTRAINTS does not properly show this constraint.

I basically want this constraint:

 ((Col1 IS NULL AND Col2 IS NULL) OR (Col1 IS NOT NULL AND Col2 IS NOT NULL))

but INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE shows this:

 ([Col1] IS NULL AND [Col2] IS NULL OR [Col1] IS NOT NULL AND [Col2] IS NOT NULL)
--^                                ^  ^                                        ^
--missing parenthesis

which is not the same and which is incorrect.

You can easily reproduce this...

create table and check constraint:

CREATE TABLE dbo.MyTest (RowID int identity(1,1) primary key, Col1 int NULL, Col2 int NULL)
ALTER TABLE dbo.MyTest ADD CONSTRAINT CK_MyTest_Cols CHECK ((Col1 IS NULL AND Col2 IS NULL) OR (Col1 IS NOT NULL AND Col2 IS NOT NULL))

display incorrect INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE:

SELECT
     c.ORDINAL_POSITION
        ,cc.CONSTRAINT_NAME
        ,cc.CHECK_CLAUSE
    FROM INFORMATION_SCHEMA.COLUMNS                                  c
        LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE   cu ON c.TABLE_CATALOG=cu.TABLE_CATALOG AND c.TABLE_SCHEMA=cu.TABLE_SCHEMA AND c.TABLE_NAME=cu.TABLE_NAME AND c.COLUMN_NAME=cu.COLUMN_NAME
        LEFT OUTER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS         cc ON cu.TABLE_CATALOG=cc.CONSTRAINT_CATALOG AND cu.TABLE_SCHEMA=cc.CONSTRAINT_SCHEMA AND cu.CONSTRAINT_NAME=cc.CONSTRAINT_NAME
    WHERE c.TABLE_SCHEMA='dbo' AND c.TABLE_Name='MyTest' AND cc.CONSTRAINT_SCHEMA IS NOT NULL

verify that the check constraint is actually working as desired:

INSERT INTO dbo.MyTest (Col1, Col2) VALUES (NULL,NULL)
INSERT INTO dbo.MyTest (Col1, Col2) VALUES (5,5)
INSERT INTO dbo.MyTest (Col1, Col2) VALUES (5,NULL)

what is up with INFORMATION_SCHEMA.CHECK_CONSTRAINTS? and is there anything I'm doing wrong or any workaround for this?

SSMS shows it incorrectly as well when using the check constraints duologue box (when Designing the table). It will also script it incorrectly as well. However, when I use the SSMS script of this constraint, the three inserts in my test (above) still work as desired (first two inserts work, third fails)? This seems contrary to everything I learned about AND, OR and parenthesis. What is going on?

Upvotes: 2

Views: 940

Answers (1)

Martin Smith
Martin Smith

Reputation: 453278

They are the same. See Logical Operator Precedence

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR.

And has higher precedence than OR so the brackets are optional - In the same way as multiplication has higher precedence than addition so 

SELECT 1 * 1 + 3 * 3

is the same as 

SELECT (1 * 1) + (3 * 3)

Upvotes: 2

Related Questions