Hello,

SQL Server Constraints can be defined as rules that govern the values that are inserted into a column. These rules help in enforcing data integrity. For example if a NOT NULL constraint is defined over a column in a particular table, it means that column will not accepted any NULL values.

Constraints used in SQL Server could be classified as follows:

1) NOT NULL
2) CHECK
3) PRIMARY KEY
4) UNIQUE KEY
5) FOREIGN KEY

Lets discuss each one briefly.

NOT NULL Constraint

NOT NULL Constraints are those that disallow a null value. The NOT NULL constraint is defined at the time the table is created. Also if a column is defined to be the Primary key column, it automatically becomes a not null column, because NULL cannot be a key for a row.

CHECK Constraint

Check constraints are those rules that enforce domain level integrity of data by limiting the values entered within a column. They can be created using any logical (Boolean) expression that returns either TRUE or FALSE, based on the operators used. CHECK constraints work at row level.

Note: The CHECK constraint can be overridden by NULL value.

They are not validated during DELETE operation.

Let us take an example. Suppose we need to enter the marks obtained by students in Science subject. The constraint applied to table dbo.Science_Marks will make sure that Max marks should not exceed 100 and Min marks should not be below 0.

PRIMARY KEY CONSTRAINT

Primary key can be defined as that value which uniquely identifies one and only one row of a table. Each row has its own primary key value. Every table has at least one column that uniquely identifies each row of the table, thus maintaining the entity integrity. Setting a column as primary key automatically enforces the NOT NULL constraint on that column.

A table can have only one Primary Key Constraint. If a Primary key constraint is applied to a column of an existing table, SQL server checks the column for any NULL value or any duplicate values, if found, Primary key cannot be defined till the redundancy is rectified or the NULL value is supplied with a valid entry.

Suppose in the previous example if we try to insert another row, having the same value for StuID column, SQL Server will throw an error.

UNIQUE KEY CONSTRAINT

A Unique Key Constraint enforces uniqueness of the values in the column over which they are defined. Even primary key constraint imposes the rule of uniqueness but it also imposes NOT NULL constraint. Talking of uniqueness, among other unique values, NULL is also a unique value, i.e. the column having a unique key constraint may allow just one NULL value (unlike Primary key that does not allow even a single NULL value).

Suppose in the previous example we want to add a Unique constraint on the StuName column, we can use the below script:

FOREIGN KEY CONSTRAINT

Also known as Referential Constraints or Referential integrity constraints, foreign key constraints are those that establish relationships between two or more tables.

1_SQL_Server_Constraints

Fig 1

In the above diagram, we can see that the two tables have ShiftID column common in them. ShiftID column is a Primary key column in Shift table, but not in EmployeeDepartmentHistory table. Here ShiftID column in Shift table is a foreign key to EmployeeDepartmentHistory table.

A foreign key column does not have to be a primary key column in the other table but should be a unique key column.

A foreign key constraint can be created during creating the table.

In case the Order table has already been created and the foreign key constraint has not been set, we could use the below script to create one:

 

Regards

Abhishek Kumar

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook