SQL constraints are the rules to restrict the type of data entered into the database. The constraints ensure data integrity and adhere to the business rules.
MSSQL has the following constraints:
NOT NULL: The column value cannot be null.
UNIQUEconstraint: Enforce unique value on a column(s) that is not a primary key.
PRIMARY KEY(PK) constraint: Column key that uniquely identifies each row in the table.
FOREIGN KEY(FK) constraint: Column key that references a primary key in another table.
CHECKconstraint: Enforce a check on the value that can be entered in the table.
A candidate key is one or more set of attributes(columns) that can uniquely identify a row in a table.
For example, the
Account number or
email_id column is a Single Candidate key.
However, you need a combination of
first name and the
last name to uniquely identify a row. Such a set of columns are called Composite Candidate key.
PRIMARY KEY (PK)
A primary key (PK) is a candidate key that uniquely identifies a row. The Entity1 Integrity constraints on a primary key are:
- A PK cannot be null.
- A PK has to be unique.
FOREIGN KEY (FK)
A foreign key is a candidate key that refers to a primary key in another table. The values in a column declared as an FK can have duplicate or null values.
FK has a Referential Integrity enforced, which enforces the rule that every value in the FK column must be present in the PK column.
The value in a column with a check constraint can only be entered when the check T-SQL constraint evaluates to
- Build apps and APIs 10x faster
- Built-in authorization and caching
- 8x more performant than hand-rolled APIs