We can say that constraints are some type of predefined set of rules that will be enforced on the data in a table and that must be followed to maintain the integrity, accuracy and reliability of the data inside a table. These rules are set because the maintaining integrity is very important, so much so that we cannot trust users and applications to enforce these rules by themselves.
Constraints are the restrictions on one or more columns of a table used to limit the data that can go into a table. If there is any violation of the constraint and the data, the action is aborted. In other words, if we insert a data that meets the constraint rule, it will be inserted successfully. And if we insert the data that violates constraint rule, the insert operation will fail and be aborted.
The primary job of a constraint is to enforce a rule in the database and they are helping query optimizer to build high-performance query execution plans.

There are three different ways of creating a constraint: it can be specified when the table is created with the CREATE TABLE statement; it can be created using ALTER TABLE statement after the table is created; it can be created through the SQL Server Management Studio (SSMS).
If the constraint is created with ALTER TABLE statement, then it first checks the existing data prior to creating that constraint. If existing data violates constraint rules, then it will not be created.
All constraints must have a unique name and if we do not assign any name to constraint, SQL Server will automatically provide it with a unique name.

Constraints cannot be altered, they only can be dropped and then recreated again. Even when we have an option in SSMS to modify constraint and we can alter expressions of constraints, the SSMS only drops and recreates constraint in the background.
Sometimes there are special situations when it is convenient to temporarily disable the rules of constraints. For example, to load initial values into a table, without worrying with foreign key and check constraints until all of the data have finished loading. If we need to insert some data that are violating constraint rule, and we don’t want to permanently drop entire constraint, the other option is to temporarily disable that constraint and then enable it later. The only constraints we can disable are the foreign key constraint, and the check constraint. Disabling a constraint is done with the ALTER TABLE command with the NOCHECK CONSTRAINT option. We can disable particular constraint in the table or all constraints on some table or even all constraints on all tables in our database. When a disabled constraint is re-enabled, the database does not check data to ensure any of the existing data meets the constraints. But we can issue a COMMAND DBCC CHECKCONSTRAINTS (TableName) to check for constraint violations. This command will affect the SQL Server performance due to not utilizing a database snapshot, so it is best not to run it during the peak hours. We can only disable check constraints and foreign key constraints.

There are two levels of constraints. First is column level. It is specified as part of a column and it applies only to that column. Second is a table level that applies to the whole table and the constraint rules can be applied to more than one column.

We have several types of constraints that can be applied to a table. They are the Unique constraints, Primary key constraint, Foreign key constraint, Check constraint and Default constraint. To that list, we can add a Not null constraint even it is not listed by Microsoft as a constraint.

To ensure consistency and accuracy of the data, we are using integrity constraints. We can divide them into three categories: Referential integrity, Entity integrity and Domain integrity.
 
Referential integrity ensures the preservation of relationship between the tables while modifying data. Data integrity is handled through the concept of referential integrity. Referential integrity is applied to a FOREIGN KEY constraint.
 
Entity integrity ensures that each row in a table is a uniquely identifiable entity. Entity integrity is applied to a PRIMARY KEY constraint.
The entity integrity and the referential integrity together form the key integrity.
 
Domain integrity ensures following the defined rules for the data values inside a database. A database can enforce these rules using CHECK constraints, UNIQUE constraints and DEFAULT constraints. There are also other options available to enforce domain integrity but we will focus here only on constraints.
CHECK constraints enforce domain integrity by limiting the inserted values to the ones that follow some defined rule.
Unique constraints enforce domain integrity by limiting the inserted values to the ones that are unique for specified columns.
Default constraints enforce domain integrity by specifying a value for the fields for which a user did not insert any value. This way it prevents inserting null values into a table.

Unique constraint ensures uniqueness of all values in a column or combination of columns. It does not allow inserting two or more identical values in the same column. Primary key also requires a unique value to be inserted but unlike a unique constraint, primary key does not allow NULL as one of the unique values. We can have more than one unique columns in a table, but only one primary key which by default has a unique constraint defined on it. That is why is better to use a unique constraint instead of a primary key constraint when we want to enforce the uniqueness.

A unique constraint uses an index to ensure a column (or set of columns) contains no duplicate values. The index that is automatically created when defining a unique constraint will guarantee that no two rows in that table will have the same value for the columns participating in that index. If we want to drop the unique index, we first have to drop a unique constraint.

The primary key constraint is a rule set on one column or multiple columns that enforce uniqueness for each row in a table. Unlike the unique constraint, a primary key cannot contain a NULL value and there can be only one primary key defined per each table. We can say that a primary key is a combination of a unique and not null constraints. By always being able to uniquely identify the record in the table ensures data integrity and entity integrity of a table.
Because of uniqueness, the table cannot have the same primary key value for two rows. But, if the primary key is defined on multiple columns, we can insert duplicate values on each column individually. In that case, the combination values of all primary key columns must be unique.

Usually, the primary key consists of one column, but it can be combined from several columns. When a primary key is set on more than one column, then it is called a composite key. The Candidate key is a column or combination of multiple columns that qualify as a primary key value.
A Recommendation is to use int columns as a primary key.
The primary key is also used to index the data inside the table. The index is automatically created when defining the primary key and it will enforce the data uniqueness.

Foreign key is a constraint that uniquely identifies a row or a record in any of another table. It is used to establish and enforce a link between two tables and create a parent-child relationship. This link requires the use of a “lookup table” which contains the accepted list of values. Any data modifications to the fields that are in parent-child relation will cause a validation to ensure that the data being updated or inserted is contained in the lookup table. If we want to insert a value for foreign key in a child table, that same value has to exist first in a parent table. Also, if we want to delete a value of primary key in parent table to which foreign key references, we must first remove all corresponding values from child tables. That is how the referential integrity between tables works.

A foreign key in child table points to the primary or candidate key in the parent table. Foreign key ensures that for every value in one table, there is a corresponding primary key value in another table. A foreign key column can also have reference to the unique key column of another table. The Foreign key constraint is also used to restrict actions that would destroy links between tables. We cannot insert a value for a foreign key, except a NULL, if there isn’t a candidate key with that value.
Difference between primary key and foreign key is that there can only be one primary key per table, but we can have multiple foreign keys defined on one table that references multiple other tables. Also, the foreign key allows inserting NULL values if there is no not null constraint defined, but the primary key does not accept NULL as a value.

With foreign key, we can choose what action will be taken when the referenced value in a parent table is modifying. For that, we have two clauses: ON DELETE and ON UPDATE. On delete clause occurs when we are trying to delete a row to which existing foreign key point. On update clause defines actions when we try to update a candidate key value to which existing foreign key points. The supported actions on these two clauses are:

NO ACTION specifies that the performed delete or update in the parent table fails with an error.
CASCADE specifies that all the rows in child table will be reflected with the related values in the parent table. In the example, if we delete a record in a parent table, all the records from child tables, that are referenced to primary key of that row from parent table, will be also deleted.
SET NULL specifies that all the rows in child table will be set to null when the referenced values in the parent table are deleted or modified.
SET DEFAULT specifies that all the rows in child table will be set to their default values when the referenced values in the parent table are deleted or modified.
If we don't use any of the above clauses and actions, then we cannot delete data from the parent table for which data in child table exists.

Check constraint is used to restrict inserting certain values into a table. It performs check on the values, before storing them into the database to ensures that a value stored in a column satisfies a specific condition. The check constraint is a layer of protection for the data.

We can specify a condition on one column or multiple columns, or even had multiple check constraints on one column. That condition is evaluated as Boolean (true, false or unknown) on all entered values. If a value is evaluated as false, then it will be rejected, and inserting data will fail. It is possible to insert a null value with check constraint and in that case, if a null value is present in the condition, the check constraint will be evaluated as UNKNOWN without throwing any error.

Specifying a condition for check constraint is similar to building a WHERE clause. We can use same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. And we can also build expressions around AND and OR operators.
Although check constraints are the easiest way to enforce domain integrity in a database, they also have some limitations: They cannot reference a different row in a table and they cannot reference a column in a different table.
 
Example of creating a check constraint on a table level:

This constraint sets a default value for a column. Default value is a value that will be inserted in the column if there is no explicitly specified value when inserting new records in a table.
For the default value we can assign a constant value, the value of a system function, or NULL. We can use a default on any column except IDENTITY columns and columns of data type timestamp.
Because the default constraint provides insert of proper values for the columns, we can say that it helps in maintaining the domain integrity of database.

Not null constraint specifies that we cannot insert a null value in a column. That is, we will not be allowed to insert a record in the table without specifying some value for that column. A null value means that no entry has been made, and differs from zero (0), blank or zero-length strings (‘’).

Not null maybe is not a strictly a constraint, but the decision to allow or not allow a null value to be inserted in a column is a type of rule enforcement for domain integrity. Generally, all columns are predefined as NULL columns meaning that they are able to hold null values. But that depends on a current configuration setting on the server and some database rules. That is why it is recommended to always explicitly define a column as null or not null to avoid problems while moving between different server environments.

This is the only constraint that cannot be defined at table level, only at column level.
For unique constraint we can declare columns to allow NULL values. But on a single column unique constraint, the database allows only one row to have a NULL value. And as we mentioned before, a primary key requires a NOT NULL setting and if we did not specify it explicitly, it will be set to this setting by default.

We now know what constraints are in SQL server and what purpose they have. 
We have seen how to use them so they can help us customize our database.
They are important to keep integrity of database, and with that help us by reducing the time needed to maintain data integrity, accuracy and reliability of a database.