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.

For example:

The range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range.

The logical expression would be the following: salary >= 15000 AND salary <= 100000.

You can apply multiple CHECK constraints to a single column.

You can also apply a single CHECK constraint to multiple columns by creating it at the table level.

For example, a multiple-column CHECK constraint can be used to confirm that any row with a country/region column value of USA also has a two-character value in the state column. This allows for multiple conditions to be checked in one location.

While creating table :

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')    
)

Adding to existing table :

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

Droping constraint :

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

To modify a CHECK constraint, you must first delete the existing CHECK constraint and then re-create it with the new definition.

Table Level Constraints.

In this type the constraint is checked if there is any modification to a row, regardless the value of the column changed or not.

One good example of this type is the check constraint, if we create check constraint in table level the constraint will be checked each time the row has been affected by any type of change.

Below are the three columns from table “card_number”.

Lo-card_number

High_card_number

Last_card_number

Table has a table level check constraint as mentioned below:

Last_card_number>= Lo-card_number And Last_card_number <= High_card_number

As you see in the above code the Last_card_number column has check constraint to check if it between Lo-card_number and High_card_number, either the value of the checked column modified or any other column is modified (Lo-card_number and High_card_number) the check constraint will be evaluated.

Note:To check whether constraint is a table level constraint,the parent_obj column from sysobjects hold the object ID(table which hold this check constraint).

select parent_obj from sysobjects where xtype = 'C' and name='checkconstranit_name'

Column Level Constraints.

In this type the constraint is checked when the value of the column changed.

CONSTRAINT COLUMNLEVELCONSTRIANT CHECK([credit_card_SW] in(0,1))

In this example only when u modify or insert a value into this column this check constraint will evaluate.

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.

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.

SELECT * FROM chk2_test

col1        col2

----------- -----------

20          100

20          100

 ALTER TABLE chk2_test WITH NOCHECK ADD CONSTRAINT nocheck_test CHECK (col1=50)

                

 SELECT * from sys.objects WHERE type='C' AND name='nocheck_test'   --MS 2000

Or

Select * from sys.check_constraints   --MS 2008

select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS  --MS 2000/2008

OBJECT_ID('constraint_name') IS NOT NULL

From the table "sys.check_constraints"

The column IS_Disabled has 0 and

Is_not_trusted has 1 because this constraint won’t check existing records.

Note: “WITH NOCHECK” creates an check constraint with out checking existing records.

Disabling CHECK Constraints (NOCHECK):

You can disable existing CHECK constraints for specific operations, such as INSERT operations, UPDATE operations, and replication processing.

• INSERT and UPDATE statements

Disabling a CHECK constraint enables data in the table to be modified without being validated by the constraints. Disable a CHECK constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.

• Replication processing

Disable a CHECK constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are typically, but not necessarily, on separate servers. If the CHECK constraints specific to the source database are not disabled, they may unnecessarily prevent new data from being entered in the destination database.

SELECT * FROM chk2_test

col1        col2

----------- -----------

20          100

20          100

ALTER TABLE chk2_test NOCHECK CONSTRAINT nocheck_test

-Disable the existing constraint

INSERT INTO chk2_test VALUES(30,30) 

SELECT * FROM chk2_test

col1        col2

----------- -----------

20          100

20          100

30          30

Select * from sys.check_constraints

From this table the column IS_Disabled has 1.

Note: you cannot directly add a Disabled Constraint to a table.

You will need to create the check Constraint first then only you can disable it by using another ALTER TABLE statement.

The difference between the WITH NOCHECK and NOCHECK are

• “WITH NOCHECK” creates an check constraint with out checking existing records.
• “NOCHECK” disable the existing constraint this constraint won’t evaluate while inserting data into those columns.

Enabling check constraints From Disable Status:

ALTER table chk2_test nocheck constraint nocheck_test   --Disabling Constraint

SELECT * FROM sys.check_constraints

Is_disables    = 1

Is_not_trusted = 1

ALTER table chk2_test with check check constraint nocheck_test  --To enable constraint

Error: Msg 547, Level 16, State 0, Line 1

The ALTER TABLE statement conflicted with the CHECK constraint "nocheck_test". The conflict occurred in database "Northwind", table "dbo.chk2_test", column 'col1'.

Because if you trying to enable the check constraint it will check existing data also, if table has any data voilates the check constraint it won’t enable the constraint.

delete from chk2_test where col1<>50            --deleting table data voilates constraint.

ALTER table chk2_test WITH CHECK CHECK CONSTRAINT nocheck_test

SELECT * FROM sys.check_constraints

Is_disables    = 0

Is_not_trusted = 0

Limotation 1:

Understanding What Happens with Null values:

Let’s cpnsider an check constraint

          ALTER TABLE dbo.Payroll

          WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType

          CHECK (SalaryType in ('Hourly','Monthly','Annual'));

Now say you run the following INSERT statements:

      INSERT INTO dbo.Payroll values (1, 1, 'Hourly',25.00);

      INSERT INTO dbo.Payroll values (2, 2,  NULL, 25.00);

      INSERT INTO dbo.Payroll values (3, 3, 'Horly',25.00);

What do you think will happen? Will only the first INSERT statement work? What about the second and third INSERT statement? Will they both violate the CK_Payroll_SalaryType? Turns out only the third INSERT statement will fail.

It fails because the SalaryType has been typed wrong, and is not “Hourly”, “Monthly”, or “Annual”. Why did the second INSERT not equate to false? Clearly, “NULL” is not in the valid list of SalaryTypes. The reason the second INSERT statement worked is that the CK_Payroll_SalaryType constraint did not equate to FALSE when the second INSERT statement was run. Because of this, the database engine inserted the record. So why did this happen? This happened because when NULL is used in a comparison operation it equates to UNKNOWN.

Since UNKNOWN is not FALSE there is no violation of the check constraint. Therefore, you need to be careful when you write your check constraints where you want to reject values that contain NULLS. Another way to code the above constraint so a NULL value is rejected for SalaryType is to write your check constraint like this:

      ALTER TABLE dbo.Payroll

          WITH NOCHECK ADD CONSTRAINT CK_Payroll_SalaryType

          CHECK ((SalaryType in ('Hourly','Monthly','Annual'))

                  and SalaryType is not NULL);

Another alternative is to make the SalaryType a NOT NULL field. When you do this you will not get a check constraint violation, but instead you will get an error that indicates you cannot insert a NULL value into your table.

Limotation 2:

A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. A CHECK constraint works at the row level. 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.

Limitation 3:

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.

sys.check_constraints:

Contains a row for each CHECK constraint object, with sys.objects.type = 'C'.

Name: name of the check constraint

Object_id:Object identification number which is unique within the data base

Principal_id: ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

Is NULL if there is no alternate individual owner.

Is NULL if the object type is one of the following:

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

TA = Assembly (CLR-integration) trigger

TR = SQL trigger

UQ = UNIQUE constraint

Schema_id: ID of the schema that the object is contained in.

Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas.

Parent_object_id: ID of the object to which this object belongs.

0 = Not a child object.

If we create a check constraint on a table this column holds the table object id.

Type: ‘C’

Type_desc: check constraint

Create_date

Modified_date:

Is_ms_shipped:  Object is created by an internal SQL Server component.

Is_published:  Object is published.

Is_schema_published: Only the schema of the object is published.

Need Further Study:

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.