当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Table Check Constraint allows invalid data

问题描述:

When I create a check constraint using the test script below, then data which violates the constraint is still allowed into the table, and the constraint is still shown as trusted.

I realize that the check constraint does not check for NULLs correctly (it includes column = null instead of column IS null), but I would still expect that SQL Server would not allow the 'ASDF', '3', or NULL values, because the check condition evaluates as false against these values. Can someone explain why this check constraint is allowing the following values: NULL, '3', 'ASDF'?

If I change the constraint condition to (checkMe is null or checkMe = '1' or checkMe = '2'), then it works as expected.

SQL Server version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

CREATE TABLE dbo.testCheck(checkMe varchar(50));

go

insert data into the table

INSERT INTO dbo.testCheck(checkMe)

VALUES ('1'),('2'),(NULL),('3');

GO

add constraint, with check so that existing data should be checked. I would expect that both the NULL and '3' violate this check, but it somehow succeds.

ALTER TABLE dbo.testCheck WITH CHECK

ADD CONSTRAINT ck_testCheck

CHECK (checkMe = null or checkMe = '1' or checkMe = '2');

GO

Attempt to insert invalid data after check constraint has been add... this succeeds?

INSERT INTO dbo.testCheck(checkMe) VALUES('ASDF');

GO

Show the table contains invalid data, and that this constraint is marked as trusted, meaning all data in the table has been verified against the constraint

SELECT *

--this is the same logic as in the check constraint, shows 3 rows that do not pass

, checkConstraintLogic = case when (checkMe = null or checkMe = '1' or checkMe = '2') then 'PASS' else 'FAIL' end

FROM dbo.testCheck;

go

SELECT parentObject = isnull(OBJECT_SCHEMA_NAME(k.parent_object_id) + '.', '') + OBJECT_NAME(k.parent_object_id)

, k.name, k.is_not_trusted

FROM sys.check_constraints k

WHERE k.parent_object_id = object_id('dbo.testCheck')

ORDER BY 1;

GO

Script output:

网友答案:

Check constraints are different from a WHERE clause in that CHECK allows a modification if the expression evaluates to a null marker. TO clarify: A WHERE clause filters out rows for which the expression evaluates to false or a null marker; a Check constraint only filters out modifications that evaluate to false.

The expression you have written always evaluates to null, since it has a comparison to null inside it. Change = null to is null.

Additionally, different usages of Check constraints have different defaults for CHECK/NOCHECK, so you should get in the habit of always specifying it.

Try the following.

ALTER TABLE dbo.testCheck WITH CHECK 
WITH CHECK
ADD CONSTRAINT ck_testCheck 
CHECK (checkMe IS null or checkMe = '1' or checkMe = '2');

Edit: Re "but I'm wondering why this example evaluates to True", as you've worked out, your example evaluates not to true but to a null marker, which the Check constraint allows. Sorry, I should have explained it a bit better.

网友答案:

As specified here:

CHECK constraints reject values that evaluate to FALSE.

In other words:

A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table.

In your case, the expression (checkMe is null or checkMe = '1' or checkMe = '2') evaluates to NULL (aka Unknown), and the CHECK constraint returns TRUE ( as the condition it is checking is not FALSE)

A good example from the same source:

For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn = 10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.

分享给朋友:
您可能感兴趣的文章:
随机阅读: