Constraints in SQL Server

Constraints are rules that the SQL Server uses to enforce data integrity in tables. SQL Server database engine runs these validations on the column values before it inserts or updates them. Hence helping in maintaining the integrity, accuracy & reliability of the data

Need for Constraints in SQL Server

The Data types in SQL Server stops you from entering invalid data into a column. For Example, you cannot insert strings into numeric or integer columns.

But that does not prevent users from entering invalid data. For Example, the user may enter a negative number in the salary field. The same employee code may be given to two employees. Users may not provide a value to a required field like name, email, etc.

The Constraints help us prevent those from happening.

List of All SQL Server Constraints

There are six constraints in SQL Server.

  1. Default Constraint
  2. NOT NULL Constraint
  3. Primary Key in SQL Server
  4. Foreign Key in SQL Server
  5. Unique Key in SQL Server
  6. Check Constraint

We create constraints when we create a table. We can also add constraints to the existing table using the Alter Table statement. There are two ways we can add a constraint to a table. Either at the column level or at the table level.

The column level constraints are specified along with the column definition in a create table statement. The following example shows the syntax & example. Note that constraint will apply to the column on which we define it. You cannot refer to other columns in column-level constraints.

We can also define the constraint at the table level. The basic syntax is shown below. If the constraint spans multiple columns, then defining the constraint at the table level is the only option you have.

Default Constraint

We use the Default Constraint in SQL Server to specify default values for a column. SQL Server uses the default value when the value for that column is absent in the insert query.

Example of default constraint at the column level.

Example of default constraint at the table level.

Adding default constraint to an existing column.

Read more at Default Constraint in SQL Server

NOT NULL Constraint

NOT NULL Constraint in SQL Server specifies that the column cannot store a NULL value. All inserts & updates to the column must specify a value. Attempting to insert or update NULL value will result in the error.

Example of NOT Null constraint

Read More about NOT NULL Constraint

Primary Key Constraint

A primary key in SQL Server is a field (or combination of fields) in the table that uniquely identifies each row or record

Example of Primary Key Constraint

At table level

Composite Primary Key

Adding Primary key to an existing table.

Read More about Primary Key Constraint

Foreign Key Constraint

Foreign key in SQL Server is used to establish the relationship between two tables

Composite Foreign Key

Read More about Foreign Key Constraint

Unique Key Constraint

Unique Constraint or Unique key in SQL Server enforces the uniqueness of the column value. i.e. no two rows of a table can have the same values.

The query to apply Unique Constraint

Composite Unique Key

Naming the Unique Key

Adding Unique Key to Existing Table

Read more about Unique Constraint

Check Constraint

The check constraint in SQL Server allows us to validate data values that are being inserted or updated in one or more columns. If the validation fails, then SQL Server will not insert or update the data

Naming the Constraint

Adding check constraint to an existing table

Read more about Check Constraint

Query to find All Constraints in SQL Server

Source

Drop a Constraint

To Drop a constraint first, find out the name of the constant using the query from the previous section and use the Alter table Drop constraint query to delete it.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top