Constraints in SQL Server

What is a constraint?

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

What are the categories of constraints to enforce the data integrity?

The following categories of the data integrity exist:

1.Entity Integrity :
Entity Integrity ensures that there are no duplicate rows in a table.

2.Domain Integrity :
Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.

3.Referential integrity :
Referential integrity ensures that rows cannot be deleted, which are used by other records (for example,corresponding data values between tables will be vital).

4.User-Defined Integrity :
User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories. Each of these categories of the data integrity can be enforced by the appropriate constraints.

Microsoft SQL Server supports the following constraints:

  1. PRIMARY KEY
  2. UNIQUE
  3. FOREIGN KEY
  4. CHECK
  5. NOT NULL
A PRIMARY KEY constraint
IT is a unique identifier for a row within a database table. Every TABLE should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint
IT enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Each TABLE can have only one primary key. If there are multiple UNIQUE identifiers for a multiple columns, such column pairs are often referred to as alternate keys or candidate keys (these terms are not used by SQL Server). In practice, one of two columns is logically promoted to primary key using the PRIMARY KEY constraint, and the other is usually declared by a UNIQUE constraint. Internally, PRIMARY KEY and UNIQUE constraints are handled almost identically.

A FOREIGN KEY constraint
prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one TABLE points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A FOREIGN KEY is a column whose values are derived from the PRIMARY KEY or UNIQUE KEY of some other table.

By using ON DELETE CASCADE option and if a user deletes a record in the master table, all corresponding recording in the detail TABLE along with the record in the master TABLE will be deleted.

           CREATE TABLE orders(order_id INT NOT NULL PRIMARY KEY,
                                            cust_id int NOT NULL REFERENCES customer(cust_id) ON DELETE CASCADE)

A TABLE can have a maximum of 253 FOREIGN KEY references. This limit is derived from the internal limit of 256 tables in a single query. If you’re dropping tables, you must drop all the referencing tables or drop the referencing FOREIGN KEY constraint before dropping the referenced table.

           DROP TABLE orders DROP TABLE customer

CHECK constraint
It is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
Check constraints allow us to define an expression for a TABLE that must not evaluate to FALSE for a data modification statement to succeed.

Check constraints deal only with some logical expression for the specific row already being operated on, so no additional I/O required.

          CREATE TABLE employee(emp_id INT NOT NULL PRIMARY KEYCHECK(emp_id between 0 and 1000),
                    emp_name VARCHAR(30) NOT NULL constraint no_numsCHECK(emp_name not like ‘%[0-9]%’),
                    entered_date datetime NULL CHECK(entered_date>=CURRENT_TIMESTAMP),
                    dept_no INT CHECK(dept_no < 0 and dept_no > 100))

NOT NULL constraint
Enforces that the column will not accept NULL values. The NOT NULL constraints are used to enforce domain integrity, as the check constraints.

What about DEFAULT Constraints?

A default allows you to specify a constant value, NULL or the run-time value of a system function if no known value exists or if the column is missing in an INSERT statement.

CREATE TABLE employee(emp_id INT NOT NULL PRIMARY KEY DEFAULT 1000 CHECK(emp_id between 0 and 1000), emp_name VARCHAR(20) DEFAULT 'Radha Krishna')
Insert into employee values(DEFAULT, DEFAULT)

The order of Integrity checks is as follows:

  1. Defaults are applied as appropriate.
  2. NOT NULL violations are raised.
  3. CHECK constraints are evaluated.
  4. FOREIGN KEY checks of referencing tables are applied.
  5. FOREIGN KEY checks of referenced tables are applied.
  6. UNIQUE/PRIMARY KEY is checked for correctness.
  7. Triggers fire.

Mr. Mansoor Ali Mohammed - Software Engineer
I love to write articles. Programming languages fascinate me and I feel at least reasonably familiar with Sql Server database and .Net Technologies. The idea of articles come from my own experiences while working in those areas and i like to share my knowledge on database with all others so that it might be helpful.
http://www.jaan1762.blogspot.com
Read moreRead more about Contributor