Constraints in SQL Server
Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.
SQL Server supports the following classes of constraints:
- NOT NULL, which specifies that the column does not accept NULL values
- CHECK constraints, which enforce domain integrity by limiting the values that can be put in a column
- UNIQUE constraints, which enforce the uniqueness of the values in a set of columns
- PRIMARY KEY constraints, which identify the column or set of columns that have values that uniquely identify a row in a table
- FOREIGN KEY constraints identify and enforce the relationships between tables
The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
In order to go deeper, look this article https://technet.microsoft.com/en-us/library/ms189265(v=sql.105).aspx
CHECK constraints are simply conditional statements (predicates that return TRUE or FALSE) that refer to table columns for maintaining data integrity. When one inserts data into a column or several columns in a single row, CHECK constraints come into action. They evaluate the data to be inserted. In case the data does not meet the condition specified in the CHECK constraint, the insertion fails.
For more information, see http://codingsight.com/check-constraints-in-sql-server/
A unique constraint is a rule that restricts column entries to unique. In other words, this type of constraints prevents inserting duplicates into a column. A unique constraint is one of the instruments to enforce data integrity in an SQL Server database. Since a table can have only one primary key, you can use a unique constraint to enforce the uniqueness of a column or a combination of columns that do not constitute a primary key.
To look into this type of constraints, look the following article http://codingsight.com/insight-into-sql-server-unique-constraints/
No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key. We recommend using a small, integer column as a primary key. Each table should have a primary key. A column or combination of columns that qualify as a primary key value is referred to as a candidate key.
See https://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx to go deeper with PRIMARY KEY constraints.
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.
And this article https://technet.microsoft.com/en-us/library/ms175464(v=sql.105).aspx opens FOREIGN KEY constraints type.
