Table Level VS Column Level Constraints

Table Level VS Column Level Constraints:

In this article we will differentiate between column level constraints and row level constraints.

Constraint is a way of enforcing rules in the database, and it maintains the integrity of the database.

There are many types of constraints:

(More information: http://msdn.microsoft.com/en-us/library/ms189862%28v=sql.105%29.aspx)

·        Primary key.

·        Foreign key.

·        Not null.

·        Check.

·        Unique.

·        Default constraints.

The syntax of each constraint is really obvious but how to use it and where it applicable or not.

And we may divide constraint into:

1.     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.

CREATE TABLE [TABLELEVEL]

(

      [ID] INT PRIMARY KEY,

      [STARTDATE] DATE NOT NULL,

      [ENDDATE] DATE NOT NULL,

      [CHECKED] DATE NOT NULL,

      CONSTRAINT TABLELEVELCONSTRIANT CHECK( [CHECKED] BETWEEN [STARTDATE] AND [ENDDATE])

)

GO

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

The primary key constraint always evaluate if any value in the whole table (not on the column or row) is changed if we have composite primary key we need to check all columns to check if it meet the constraint or not and the new added or modified value does not make any conflict with other row.

2.     Column Level Constraints.

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

If we consider the previous example of check constraint a little change to the code definition will make the constraint be checked on the column not on the row.

CREATE TABLE [COLUMNLEVEL]

(

      [ID] INT PRIMARY KEY,

      [STARTDATE] DATE NOT NULL,

      [ENDDATE] DATE NOT NULL,

      [CHECKED] DATE NOT NULL,

      CONSTRAINT COLUMNLEVELCONSTRIANT CHECK( [CHECKED] > '2012-01-01')

)

GO

Note we changed the check constraint to check for specific value that doesn’t depend on any other column on the table.

To sum up if the constraint depends on other column or we need to check other column(s) or rows this constraint will be table level constraint.

Written by:

Elmozamil Elamir Hamid.