SQL Server Constraints

What are constraint in SQL Server

Constraints :  are used to maintained database integrity, basically constraints are rule by which we restrict the data to be entered into tables.

There are two types of constraints.

  • Table level constraints:  use to limit the whole table data.
  • Column level constraints : use to limit only column data.

Constraint are used to maintained database integrity,  some SQL Server constraints and its used are defined as below.

  • NOT NULL : Specifies that the column does not accept NULL values.
  • UNIQUE     :  Enforce the uniqueness of the values in a set of columns. Means no two rows have same values in table. Primary Key also enforce uniqueness of column but unique key allow one NULL values.
  • PRIMARY KEY : identify the column or set of columns that have values that uniquely identify a row in a table. It will not allow any null values and by default Clustered index is created with unique key. If you put more than one column for combination of primary key that is called composite primary key.
  • FOREIGN KEY : constraints identify and enforce the relationships between tables. This is used to maintain referential integrity. 
    • You cannot insert a row with a foreign key value, except NULL.
    • The ON DELETE clause has the following options:
      • NO ACTION specifies that the deletion fails with an error.
      • CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.
      • SET NULL :specifies that all rows with foreign keys pointing to the deleted row are set to NULL.
      • SET DEFAULT :specifies that all rows with foreign keys pointing to the deleted row are set to their default value.
  • CHECK  : Its used to limiting specified value that can be put into column, this is used to maintain domain integrity. 
  • DEFAULT : Use to set default value of a column when user have not supplied any external value.

Syntax Examples: