What are SQL Server Integrity Constraints?
Integrity constraints are always applied on the table or column based.
The main function of Integrity Constraints is to restrict the type of information that can reach the table, which helps the database to follow business rules.
Also, with the help of the SQL Server Integrity Constraints, you make sure that all the information present in the database is reliable, and it is also accurate.
Integrity constraints require that every SQL statement that adds, modifies, or deletes data be performed in a way that does not compromise data integrity. Therefore, integrity constraints are used to protect the database from accidental damage.
Types of integrity constraints
The types of constraints for SQL Server are:
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
Primary Key
The PRIMARY KEY constraint consists of a column or columns with values that uniquely identify each row in the table.
The SQL PRIMARY KEY constraint is a kind of mix between the UNIQUE and SQL NOT NULL constraints, where the column or
table participating in the PRIMARY KEY cannot accept the value NULL.
If the PRIMARY KEY is defined in multiple columns, then duplicate values can be inserted into each column individually,
but it is important to mention that the combined values of all the PRIMARY KEY columns must be unique.
Don't forget that you can only define one PRIMARY KEY per table, and it is recommended to use small columns in the PRIMARY KEY,
to make everything work better.
Foreign Key
The FOREIGN KEY constraint is used to link tables in a SQL Server database.
The FOREIGN KEY constraint in one table connects to the primary key in another table from the same database.
The FOREIGN KEY helps to identify another table record and association from one table to another.
UNIQUE
One of the important constraint is called UNIQUE constraint which help to filter unique values and remove duplicate values from the column.
The UNIQUE constraint has the purpose to ensure that no duplicate values are inserted into a specific column or
table that participate in the UNIQUE constraint and are not part of the PRIMARY KEY.
In other words, the index that is automatically created when you define a UNIQUE constraint is in charge of guaranteeing
that the same value won’t appear for two (or more) columns participating in that chosen index.
CHECK
The CHECK is used to set rules about the values of data that are allowed in one or more columns of a table in the database.
NOT NULL
Columns contain NULL values when the automatic configuration is not modified, this is simply how SQL Servers are made to work.
For the same reason, you can use a NOT NULL constraint to avoid inserting NULL values into the specified column.
This means that you just have to apply a valid SQL NOT NULL value to that column in the INSERT or UPDATE statements since the column will always contain data.
It is important not to forget that if the null capability is not specified when defining the column, the column will accept the value NULL by default.