CHECK Constraints:
CHECK constraints enforce domain integrity
by limiting the values that are accepted by one or more columns.
You can create a CHECK constraint with any
logical (Boolean) expression that returns TRUE or FALSE based on the logical
operators.
The nice thing about CHECK constraints is
that they are not restricted to a particular column. they can be Related to a column, but they can
also be essentially table related in that they can check one column Against another as long as all the columns
are within a single table, and the values are for the same row Being updated or inserted.
They may also check that any combination
of column values meets a criterion.
CHECK constraints are similar to FOREIGN KEY
constraints in that they control the values that are put in a column.
The difference is in how they determine
which values are valid: FOREIGN KEY constraints obtain the list of valid values
from another table, and CHECK constraints determine the valid values from a
logical expression.
The constraint is defined using the same
rules that you would use in a WHERE clause.
The CHECK constraints are woefully under
used in most data models. In a lot of cases, I've seen a trigger used to
enforce a business rule that could have been enforced using a CHECK.
It is better to use CHECKs over triggers
for a variety of reasons.
For one reason, don't need to waste time
to write specialized code.
Another reason is that CHECK constraints
are enforced before a database modification is made by the database engine
whereas a trigger is checked after the fact.
Using triggers extends the life of a
transaction and can potentially be expensive if a ROLLBACK condition is
detected.
The following table gives examples of the
criteria for a CHECK constraint:
Goal SQL
Limit Month column to appropriate numbers BETWEEN
1 AND 12
Proper SSN formatting LIKE ‘[0-9][0-9][0-9]-[0-9]
[0-9]-[0-9][0-9][0-9][0-9]’
Limit to a specific list of Shippers IN (‘UPS’, ‘Fed Ex’, ‘USPS’)
Price must be positive UnitPrice >= 0
Referencing another column in the same row
ShipDate >= OrderDate
Almost anything you Could put in a WHERE clause you can also
put in your constraint.
What’s more, CHECK constraints are very Fast
performance-wise compared to the alternatives (rules and triggers).
Caution1:
Constraints that include implicit or
explicit data type conversion may cause certain operations to fail.
For example, such constraints defined on
tables that are sources of partition switching may cause an ALTER
TABLE...SWITCH operation to fail. Avoid data type conversion in constraint
definitions.
Caution 2-Handling Null Values in check constraint:
Enforcing Data Integrity with check
Constraints:
Let us consider the boxes table as shown
below:
CREATE TABLE Boxes
(
Label VARCHAR(30) NOT NULL PRIMARY KEY ,
LengthInInches DECIMAL(4, 2) NULL ,
WidthInInches DECIMAL(4, 2) NULL ,
HeightInInches DECIMAL(4, 2) NULL
) ;
Let us consider our table already contain
a row inserted by this query.
INSERT INTO Boxes
(Label, LengthInInches, WidthInInches,
HeightInInches)
VALUES
( 'School memorabilia', 3, 4, 5) ;
(1 row(s) affected)
Business Rule:
How do we enforcing our new business rule
(the height of a box must be less than, or equal to, the width; and the width
must be less than, or equal to, the length)
in a constraint?
Our first attempt might look as shown
below:
ALTER TABLE dbo.Boxes
ADD CONSTRAINT Boxes_ConsistentDimensions
CHECK (HeightInInches <= WidthInInches
AND WidthInInches <= LengthInInches) ;
The attempt to add this constraint fails,
because some data in the table does not validate against it.
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint
"Boxes_ConsistentDimensions".
The conflict occurred in database
"TEST3", table "dbo.Boxes".
Existing data violates the constraint.
First, we need to remove the offending
data, as shown above.
In real life, we would probably want to
clean up that data instead of just deleting it, but we shall skip this step for
brevity.
DELETE FROM dbo.Boxes
WHERE NOT ( HeightInInches <=
WidthInInches
AND WidthInInches <= LengthInInches ) ;
Deleting invalid data.
Now just run the below code to create the
check constraint:
ALTER TABLE dbo.Boxes
ADD CONSTRAINT Boxes_ConsistentDimensions
CHECK (HeightInInches <= WidthInInches
AND WidthInInches <= LengthInInches) ;
Now, if we try to insert some invalid
data, the constraint enforces our rule and the INSERT operation fails.
Unfortunately, in its current form our
constraint is still flawed (damaged), as demonstrated by the fact that below
query succeeds in adding a box that is taller than it is long.
INSERT INTO dbo.Boxes
( Label , LengthInInches , WidthInInches ,
HeightInInches)
VALUES ( 'School memorabilia' , 3, NULL ,
5) ;
Adding a box with height greater than length.
In order to use constraints effectively,
we need to understand how they work;otherwise we may end up, as here, with a
false sense of security and some dirty data in our database.
The problem here, as you may have guessed,
is a failure to handle NULL values correctly. This is one of several very
common mistakes that are made when using constraints, and over the coming
sections we'll learn what they are, and how to avoid them. Along the way, we'll
fix our constraint so that it works reliably.
Solution to handle nulls in CHECK constraints:
Logical conditions in CHECK constraints
work differently from logical conditions in the WHERE clause.
If a condition in a CHECK constraint evaluates to
"unknown," then the row can still be inserted,
But if a condition in a WHERE clause evaluates to
"unknown," then the row will not be included in the result set.
To demonstrate this difference, add
another CHECK constraint to our Boxes table, as shown below
ALTER TABLE dbo.Boxes
ADD CONSTRAINT CHK_Boxes_PositiveLength
CHECK ( LengthInInches > 0 ) ;
The CHK_Boxes_PositiveLength constraint
ensures that boxes cannot have zero or negative length.
However, the condition used in the CHECK
constraint will not prevent us from inserting a row with NULL length, as
demonstrated below.
INSERT INTO dbo.Boxes ( Label,
LengthInInches, WidthInInches, HeightInInches)
VALUES ( 'Diving Gear', NULL, 20, 20) ;
The CHK_Boxes_PositiveLength check
constraint allows us to save rows with NULL length.
However, this row will not validate
against exactly the same condition in a
WHERE clause.
SELECT Label, LengthInInches,
WidthInInches, HeightInInches
FROM dbo.BoxesWHERE LengthInInches > 0
;
This SELECT statement does not return rows
with NULL length.
Many SQL developers get into trouble
because they fail to consider how NULL evaluates in logical expressions.
For example, we've already proven that our
constraint Boxes_ConsistentDimensions does not quite work; it validated a box
with height greater than length.
Now we know enough to understand how that
happened: the constraint will only forbid rows where the CHECK clause
(HeightInInches <= WidthInInches AND WidthInInches <= LengthInInches)
evaluates to FALSE.
If either condition in the clause
evaluates to UNKNOWN, and another evaluates to UNKNOWN or TRUE, then the
overall clause evaluates to UNKNOWN and the row can be inserted. In this case,
both conditions in our CHECK constraint evaluated as UNKNOWN.
Three valued logic:
The truth table for AND:
|
|
True
|
Unknown
|
False
|
|
True
|
True
|
Unknown
|
False
|
|
Unknown
|
Unknown
|
Unknown
|
False
|
|
False
|
False
|
False
|
false
|
The truth table for OR:
|
|
True
|
Unknown
|
False
|
|
True
|
True
|
True
|
True
|
|
Unknown
|
True
|
Unknown
|
Unknown
|
|
False
|
True
|
Unknown
|
false
|
The truth table for NOT:
|
True
|
False
|
|
Unknown
|
Unknown
|
|
False
|
True
|
SELECT CASE WHEN LengthInInches >=
WidthInInches THEN 'True'
WHEN LengthInInches < WidthInInches
THEN 'False'
ELSE 'Unknown'
END
AS LengthNotLessThanWidth ,
CASE
WHEN WidthInInches >= HeightInInches THEN 'True'
WHEN WidthInInches < HeightInInches
THEN 'False' ELSE 'Unknown'
END AS WidthNotLessThanHeightFROM
dbo.BoxesWHERE Label = 'School memorabilia' ;
LengthNotLessThanWidth WidthNotLessThanHeight
----------------------
----------------------------------------------------
Unknown Unknown
Both conditions in
Boxes_ConsistentDimensions evaluate as "unknown."
When we develop constraints, we must take
added care if the columns involved are nullable.
The below script fixes our
Boxes_ConsistentDimensions constraint for this perticulr set of value set (
'School memorabilia' , 3, NULL , 5)
DELETE FROM dbo.Boxes
WHERE HeightInInches > WidthInInches
OR WidthInInches > LengthInInches
OR HeightInInches > LengthInInches ;
GO
ALTER TABLE dbo.Boxes
DROP CONSTRAINT Boxes_ConsistentDimensions
;
GO
ALTER TABLE dbo.Boxes
ADD CONSTRAINT Boxes_ConsistentDimensions
CHECK ( (HeightInInches <=
WidthInInches
AND WidthInInches <= LengthInInches
AND HeightInInches <= LengthInInches) )
;
The fixed Boxes_ConsistentDimensions
constraint.
you'll find that, this time, the
constraint will prevent invalid data from saving. The lesson here is simple:
when testing CHECK constraints, always include in your test cases rows with
NULLs.
Now if you try to execute below insert
statement with different set of values then again the record will insert into
the table.
INSERT INTO #Boxes
(Label , LengthInInches , WidthInInches ,
HeightInInches)
VALUES ( 'School memorabilia' , NULL ,
NULL , 5) ;
We will modify the ALTER statement (to
avoid the UNKNOWN status situation) as below:
ALTER TABLE #Boxes
ADD CONSTRAINT Boxes_ConsistentDimensions
CHECK ( (HeightInInches <=
WidthInInches
AND WidthInInches <= LengthInInches
AND WidthInInches is not null
AND LengthInInches is not null
AND HeightInInches is not null )) ;
Caution 3:(Check constraint for complex business logic instead of triggers)
Let us consider following Employee table.
CREATE TABLE DBO.EMPLOYEE
(
EMPLOYEEID INT IDENTITY(1,1) NOT NULL,
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50) NOT NULL,
IS_MANAGER BIT NULL,
SALARY MONEY NOT NULL,
BONUSPCT FLOAT NOT NULL
)
GO
Our Business rule is
Managers in our company can earn any bonus
percentage but they are the only employee type that is allowed to earn a bonus
of 5 or more percent of his/her salary.
Non-managers can earn any amount provided
it's less than 5%. Let's try to enforce this company rule via a table CHECK
constraint:
Our first attempt is:
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT
CHECK (IS_MANAGER = 1 AND BONUSPCT >=
5.00)
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,
LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'GEORGE', 'WASHINGTON', 1, 100000, 5.00
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,
LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50
GO
What happened? Employee Washington
was inserted ok but Franklin
failed. Well,
the CHECK constraint as it's defined
forces all employees to be both a manager and to have a bonus percentage
greater than or equal to 5.
That's great if all employees must be
managers with a bonus of 5% or greater. However, that's not the business rule
we're trying to implement.
We're trying to make sure that if the
employee is not a manager, he/she cannot exceed the 5% bonus threshold.
So how can we have the CHECK constraint
enforce integrity conditionally? You might get tempted to use a trigger.
However, the CHECK constraint supports
CASE expressions. Furthermore, as I mentioned at the beginning of this tip,
CHECKs are examined for TRUE/FALSE
conditions. As a result, you can leverage these two together to produce a CHECK
constraint that can perform conditional checking.
Let's re-create our CHECK using these
principles and try to re-insert employee Franklin.
ALTER TABLE DBO.EMPLOYEE
DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT
GO
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT
CHECK (CASE WHEN IS_MANAGER <> 1 AND
BONUSPCT >= 5.00 THEN 1 ELSE 0 END = 0)
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,
LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'BEN', 'FRANKLIN', 0, 75000, 2.50
GO
Now 1 row inserted.
Wait,Now let's try to INSERT a new
employee, Jefferson.
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,
LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'THOMAS', 'JEFFERSON',
NULL, 80000, 7.50
GO
Jefferson
was not flagged as a manager but the engine allowed his bonus of 7.5%! Why did
this happen?
Again, recall that CHECK constraints are
examined for TRUE/FALSE conditions.
Looking at our schema, the IS_MANAGER
column is declared as NULL.
Any NULL values in this column will cause
the IS_MANAGER condition in the CHECK to equate to unknown and cause the CASE
expression to evaluate to our default boolean value for success (zero).
When working with nullable columns, this
is a gotcha to be aware about. There are a couple of ways to correct this.
One is to define the IS_MANAGER flag as
NOT NULL, converting the NULL values to zero.
If you cannot change the model, the other
is to re-write the CASE to account for NULL IS_MANAGER flags. Below is one way
to re-write the CASE (you'll probably have your own variation; my version is
for illustrative purposes).
TRUNCATE TABLE DBO.EMPLOYEE
GO
ALTER TABLE DBO.EMPLOYEE
DROP CONSTRAINT CK_EMPLOYEE_BONUSPCT
GO
ALTER TABLE DBO.EMPLOYEE
ADD CONSTRAINT CK_EMPLOYEE_BONUSPCT
CHECK (CASE WHEN IS_MANAGER IS NULL AND
BONUSPCT >= 5.00 THEN 1
WHEN IS_MANAGER <> 1 AND BONUSPCT >= 5.00 THEN 1
ELSE 0 END = 0)
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,
LASTNAME, IS_MANAGER, SALARY, BONUSPCT)
SELECT 'JAMES', 'MADISON', NULL, 60000, 5.50
GO
This insert statement will raise an
error,it invalidate the check constraint.
Creating and Modifying CHECK Constraints:
You can create a CHECK constraint as part
of the table definition when you create a table.
If a table already exists, you can add a
CHECK constraint.
Tables and columns can contain multiple CHECK
constraints.
If a CHECK constraint already exists, you
can modify or delete it.
For example, you may want to modify the
expression that is used by the CHECK constraint on a column in the table.
Note:
To modify a CHECK constraint, you must
first delete the existing CHECK constraint and then re-create it with the new
definition.
Define a CHECK constraint on single column while creating a table.
CREATE TABLE #Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
The command(s) completed successfully.
To allow naming of a CHECK constraint on single column while creating a table.
CREATE TABLE #Persons
(
P_Id int NOT NULL constraint chk_person
CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
CREATE TABLE #Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND
City='Sandnes')
)
IF
OBJECT_ID ('dbo.Vendors', 'U') IS NOT NULL
DROP TABLE dbo.Vendors;
GO
CREATE TABLE dbo.Vendors
(VendorID int PRIMARY KEY, VendorName nvarchar (50),
CreditRating tinyint)
GO
ALTER TABLE dbo.Vendors ADD CONSTRAINT
CK_Vendor_CreditRating
CHECK (CreditRating >= 1 AND CreditRating <= 5)
ALTER TABLE statement to add multiple check constraints to your table at once.
Here is an example that does that:
ALTER
TABLE #Persons
WITH NOCHECK ADD CONSTRAINT CK_person1
CHECK (city in ('india')),
CONSTRAINT CK_Person2
CHECK (p_id > 10 and p_id < 150000);
To DROP a CHECK Constraint:
To drop a CHECK constraint, use the following
SQL:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
Forcing a CHECK Constraint by Using WITH
NOCHECK:
When a CHECK constraint is added to an
existing table, the CHECK constraint can apply to new data only or to existing
data.
By default, the CHECK constraint applies
to both existing data and any new data.
Use the WITH NOCHECK option of the ALTER
TABLE statement to apply the new constraint only to newly added data.
This option is useful when the existing
data already meets the new CHECK constraint, or when a business rule requires
the constraint to be enforced
only from this point forward.
For example, an old constraint may require
that postal codes be limited to five digits but a new constraint requires
nine-digit postal codes.
Old data with five-digit postal codes is
still valid and will coexist with new data that contains nine-digit postal
codes.
Therefore, only new data should be checked
against the new constraint.
However, you should be careful when you
add a constraint without checking existing data because this bypasses the
controls in the Database Engine that enforce the integrity rules for the table.
Disabling And re-enable the CHECK Constraints:
Let us consider an temporary table
#t_inner.
create table #t_inner (id tinyint not
null, name varchar(20) )
The command(s) completed successfully.
create check constraint on column (id):
Alter table #t_inner add constraint chk_#t_inner_id check(id>10)
The command(s) completed successfully.
We are going to insert values into table
these set of values satisfy the check constraint:
insert into #t_inner values(15,'ms sql
15')
select * from #t_inner;
id name
-------------------
15 ms
sql 15
Again we are going to insert another valid
row.
Insert into #t_inner values (20,'ms sql 20')
If we look at the execution plan we can
clearly see ASSERT Showplan operator for check constraint.
Select * from #t_inner;
id
name
---- --------------------
15
ms sql 15
20
ms sql 20
(2 row(s) affected)
Disabling the check constraint :
alter table #t_inner
nocheck constraint chk_#t_inner_id
The command(s) completed successfully.
insert into #t_inner values(5,'my sql 5')
(1 row(s) affected)
Actually this insert statement contain
invalid data, because the id column value 5 <10 but we disabled the check
constraint hence this insert statement executed successfully.
Execution plan for the above insert statement:
From the execution plan also there is no
ASSERT show plan operator for the check constraint.
select * from #t_inner
id
name
---- --------------------
15
ms sql 15
20
ms sql 20
5
my sql 5
(3 row(s) affected)
Re-Enable check constraint-(with nocheck) option:
Now we are re-enable the check constraint
by (with nocheck) option,this option signifies that it won’t check existing data it will
only check future data.
Alter table #t_inner
with
nocheck
check constraint chk_#t_inner_id
The command(s) completed successfully.
insert into #t_inner values(7,'ms sql 7')
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN
CHECK constraint 'chk_#t_inner_id'. The conflict occurred in database 'tempdb',
table '#t_inner__________________________000000037EE1', column 'id'.
The statement has been terminated.
The above insert statement violate the
check constraint, because the column value id has the value 7
But our check constraint is check id>10
We are going to insert another valid row.
Insert into #t_inner values(17,'ms sql
17')
(1 row(s) affected)
Select * from #t_inner
id
name
---- --------------------
15
ms sql 15
20
ms sql 20
5
my sql 5
17
ms sql 17
(4 row(s) affected)
Re-enable the check constraint by (with check) option:
alter table #t_inner with check
check constraint chk_#t_inner_id
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with
COLUMN CHECK constraint 'chk_#t_inner_id'. The conflict occurred in database
'tempdb', table '#t_inner___________________________________000000037EE1',
column 'id'.
Now we Re-enable the check constraint by
with check option hence it will check all table rows,but we have a row with
column “id “ having the value 5 which is conflicted with the check constraint
condition hence the above statement terminated.
Can you trust your constraints?
SQL Server allows you to temporarily
disable your CHECK and FOREIGN KEY constraints.
I would not recommend this as part of your
regular production schedule, but there are certainly cases where this comes in
handy.
One example is if you’re copying thousands
of rows from another table, and you already know with 100% certainty that the data doesn’t
violate any constraint. The overhead of checking could impact
performance of your process; in the worst case making the execution time exceed
your maintenance window. Disabling the constraints can result in a tremendous
reduction of execution time.
Another example would be the case where
you’re importing lots of data from a third party; the raw data might violate
some constraints, but these errors are all corrected as part of the process
before the constraints are re-enabled.
I would personally prefer to import the
raw data to a staging table, do the cleansing there and only then copy the data to the
production table – which of course takes us back to the first
example
However, there is a large caveat that many
people seem to be unaware of – if you don’t take care while re-enabling the
constraints, you might end up disabling the query optimizer’s potential to
generate the best possible execution plan for your queries!
Let’s first look at the abridged syntax
for disabling end re-enabling a constraint:
The syntax for disable a constraint
ALTER TABLE <tablename>
NOCHECK
CONSTRAINT <constraintname>;
The syntax to re-enable the check constraint:
ALTER TABLE <tablename>
WITH {
CHECK | NOCHECK }
CHECK CONSTRAINT
<constraintname>;
Note the “WITH {CHECK | NOCHECK}” clause.
Specifying WITH CHECK signifies to SQL Server that you want it to check the
re-enabled constraint for all rows in the table; if one or more fail
to satisfy the constraint, the ALTER TABLE command fails.
Specifying WITH NOCHECK (the default for
existing constraints) means that existing rows are not checked. This is of
course faster, but it has a severe side effect that you should really be aware
of: you may know with 100% certainty that all rows in the table still abide by
the constraint, but
SQL Server doesn’t know this. As
soon as you enable a constraint without checking the existing rows, SQL Server
will mark the constraint as “not trusted”.
This means that the query optimizer will
no longer use it’s knowledge of the constraint to optimize your queries.
How optimiser using the knowledge of check constraint:
To
see the effects of this, I set up a simple test with one table, two columns,
and a CHECK constraint:
Let us consider an sample table #t_inner
with two columns.
Create table #t_inner
(
Id tinyint not null,
Name varchar (10)
)
Now we are adding check constraint on the
column id.
Alter table #t_inner
Add constraint chk_#t_inner_id check (id>15)
The command(s) completed successfully.
Insert into #t_inner values (20,'my sql
20')
(1 row(s) affected)
If I now execute a query that, because of
the CHECK constraint can’t possibly return any rows, I’ll get an execution plan
that doesn’t even touch the table:
Select * from #t_inner
Where id<15
id
name
---- ----------
(0 row(s) affected)
From the above query execution plan we can
understand optimiser not even touch the table, it’s completely using the
knowledge of check constraint.
What will happen if we disable the check constraint?
Syntax to disable the check constraint:
Alter table #t_inner
Nocheck
Constraint chk_#t_inner_id
The command(s) completed successfully.
Check whether check constraint is disable
or not, by inserting invalid data according to check constraint.
insert into #t_inner values(10,'my sql
10')
(1 row(s) affected)
Select * from #t_inner
id
name
---- ----------
20
my sql 20
10
my sql 10
(2 row(s) affected)
Yes the check constraint is disabled.
Now if we look at the execution plan of
the same previous query, now optimiser going for table scans because, the check
constraint is disabled.
Select * from #t_inner
where id<15
id
name
---- ----------
(0 row(s) affected)
Re-Enable check constraint by WITH NOCHECK option:
Querying
#t_inner table data.
Select * from #t_inner
id
name
---- ----------
20
my sql 20
10
my sql 10
(2 row(s) affected)
The #t_inner table contain two rows out of
two rows one row is valid according to check constraint,but not second because
the id column value less than the 15.
But now we are re-enable the check
constraint with [with nocheck] option hence it wont verify the existing data
,it only think about the future data going to insert in to the table.
Syntax to Re-enable the check constraint:
Alter table #t_inner
with nocheck
check constraint chk_#t_inner_id
The command(s) completed successfully.
Trying to insert invalid data:
insert into #t_inner values(12,'my sql
12')
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN
CHECK constraint 'chk_#t_inner_id'. The conflict occurred in database 'tempdb',
table '#t_inner__________________________________000000038DF4', column 'id'.
The statement has been terminated.
If we look at the execution plan now again
optimiser go for “TABLE SCAN” because we re-enable the constraint by [with
nocheck] option hence the optimiser un trust the check constraint and optimiser
won’t use the knowledge of check constraint…
Select * from #t_inner
Where id<15
Re-Enable check constraint WITH
CHECK option:
Select * from #t_inner
id
name
---- ----------
20
my sql 20
10
my sql 10
(2 row(s) affected)
Re-enable check constraint by with check
option:
This will throw an error because our table
contain one invalid record, having id column value less than 15. we inserted
this record while we disable the check constraint.
If we re enable by with check option, it
will verify all the table data.
Alter table #t_inner
With check
Check constraint chk_#t_inner_id
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with
COLUMN CHECK constraint 'chk_#t_inner_id'. The conflict occurred in database
'tempdb', table '#t_inner_________________000000038DF4', column 'id'.
To re-enable the check constraint by with
check option we need to delete that particular invalid record.
Delete from #t_inner
Where id<15
(1 row(s) affected)
Select * from #t_inner
Id
name
---- ----------
20
my sql 20
(1 row(s) affected)
Now the #t_inner table contain valid
record.
Alter table #t_inner
With check
Check constraint chk_#t_inner_id
The command(s) completed successfully.
If we look at the execution plan of same
select statement ,now our optimiser doesn’t touch the table because the query
optimiser trusted the check constraint.
Select * from #t_inner
Where id<15
So the bottom line is to
always make sure that you include the WITH CHECK option when re-enabling a
constraint. Or, to conclude with an awful pun, always double-check that there’s
a double CHECK in the command.
Limitations:
If a table that has just been created does not have any rows, any
CHECK constraint on this table is considered valid.
This situation can produce unexpected
results, as in the following example.
CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM CheckTbl
RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK
(dbo.CheckFnctn() >= 1 );
GO
The CHECK constraint being added specifies
that there must be at least one row in table CheckTbl.
However, because there are no rows in the
table against which to check the condition of this constraint, the ALTER TABLE
statement succeeds.
CHECK constraints are not validated during DELETE statements.
Therefore, executing DELETE statements on
tables with certain types of check constraints may produce unexpected results.
For example, consider the following
statements executed on table CheckTbl.
INSERT INTO CheckTbl VALUES (10, 10)
GO
DELETE CheckTbl WHERE col1 = 10;
The DELETE statement succeeds, even though
the CHECK constraint specifies that table CheckTbl must have at least 1 row.
|
name
|
sysname
|
Object name
|
|
type
|
char(2)
|
Object type. Can be one of the
following values:
|
C = CHECK constraint
The value for status in
sysobjects table is
0 when the constraint was
enabled and trusted,
2 when the constraint was
enabled and trusted,
2050 (2 + 2048) when
enabled and not trusted,
And 2306 (2 + 256 + 2048)
when disabled and not trusted.
This is the Basic query
to find whether the constraint is enabled or disabled and whether it is trusted
or not trusted?
SELECT
OBJECT_NAME(c.[id]) AS Table_Name
,o.name as constraint_name,
CASE
(OBJECTPROPERTY(o.id,'CnstIsDisabled'))
WHEN 1 THEN 'Constraint is disabled'
ELSE 'constraint is enabled'
END as constraint_status
,CASE (OBJECTPROPERTY(o.id,'CnstIsNotTrusted'))
WHEN 1 THEN 'Constraint is not trusted'
ELSE 'constraint is trusted'
END as constraint_trust_status
FROM sysobjects o
INNER JOIN sysconstraints
c on o.id=c.constid
Where o.type=’C’
order by Table_Name
From the sysobjects:
Id Object identification number
If type is “C” then it is ID of an
Check constraint
Name is name of an constraint
From sysconstraints:
Constid :id of an
constraint
Id: identification number
of an table that owns the constraint
Colid: ID
of the column on which the constraint is defined, 0 if a table constraint.
Hence the join condition
is:
Where
Sysobjects.id=sysconstraints.cunstid
To get the name the table
that check constraint belongs:
OBJECT_NAME (c.id) “we will get it from sysconstraints table”
To get the name the
column that check constraint defined:
OBJECT_NAME (c.colid) “we will get it from sysconstraints table”
To get the name of the
check constraint:
Sysobjects.name
OBJECT_NAME(sysobjects.id)
OBJECT_NAME(sysobjects.constid)
The Assert is used to
verify a certain condition, it validates a Constraint on every row to
ensure that the condition
was met.
If, for example, our DDL
includes a check constraint which specifies only two valid values for a column,
the Assert will, for every row,validate the value passed to the column to
ensure that input is consistent with the check constraint.
Assert Show Plan Operator And check
constraints
Let's see where the SQL
Server uses that information in practice. Take the following
T-SQL:
IF OBJECT_ID('Tab1') IS
NOT NULL
DROP TABLE Tab1
GO
CREATE TABLE Tab1(ID
Integer, Gender CHAR(1))
GO
ALTER TABLE TAB1 ADD
CONSTRAINT ck_Gender_M_F CHECK(Gender IN('M','F'))
GO
INSERT INTO Tab1(ID,
Gender) VALUES(1,'X')
GO
No comments:
Post a Comment