SQL Server - What are Constraints in SQL?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

Note: What data is valid and what is invalid can be defined using constraints.

How to specify constraints?

Constraints can be easily classified in the following two types-

n  Column Types Constraints: 

n  A column-level constraint references a single column and is defined along with the definition of the column at the time of creating the table using CREATE TABLE statement

n  Any constraint can be defined at the column level except for a COMPOSITE primary key constraints

n  Table Types Constraints :

n  A table-level constraint references one or more columns and is defined separately after the creation of the table using the Alter Command

n  All constraints can be defined at the table level except for the NOT NULL constraint.

Syntax: Naming of a Constraint

n  The general convention used for naming constraints is

                           <table name>_<column name>_<constraint type>

n  table name is the name of the table where the constraint is being defined,

n  column name is the name of the column to which the constraint applies,

n  constraint type is an abbreviation used to identify the constraint’s type

n  For example, a constraint name

                                          emp_deptno_fk refers to:

n  A constraint in table EMP on column DeptNo of type foreign key. 

                        dept_deptno_pk refers to:

n  A constraint in table DEPT on column DeptNo of type primary key

Most Popular Constraint in SQL

n  NOT NULL: This constraint ensures that the column has a value and the value is not a null value. A space or a numeric zero is not a null value and can defined at Column level only. click here to know How does Not Null constraint work?

n  UNIQUE: This constraint when specified with a column or set of columns be unique, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated. 

n  PRIMARY KEY: Also known as the entity integrity constraint, is a field which can uniquely identify each row in a table. It creates a primary key for the table. A table can have only one primary key constraint. If a table uses more than one column as its primary key (i.e., a composite key), the key can only be declared at the table level. 

n  FOREIGN KEY: Also known as referential integrity constraint, is a field which can uniquely identify each row in another table. This constraint is used to specify a field as foreign key and it establishes a relationship with the primary key of another table.

Foreign key and referenced primary key columns need not have the same name, but a foreign key value must match the value in the parent table’s primary key value

n  CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition

n  DEFAULT: This constraint specifies a default value for the column when no value is specified by the user. click here to know How does Default constraint work?

To watch a series of constraints, please visit our YouTube channel -

Conclusion - Constraints are rules and regulations which simply apply on the data columns to meet some business standards and strictly attached within the column definitions. They give us a surety to us the data will be inserted as per their definition and any unwanted data will not be entertained by these SQL constraints.