Introducing Constraints (constraints)

One of the important functions in SQL Server is to maintain and enforce data integrity. There are a number of means to achieve this, but one of the most commonly used and preferred methods is to use constraints. A constraint is an attribute assigned to a column or set of columns in a table to prevent some kind of inconsistent data value from being entered. Constraints are used to apply business logic rules and enforce data integrity.

Constraints can be created when the table is created, are part of the table definition, can be included in the table during creation, or included after creation. Constraints can be classified as column constraints and table constraints:

• Table Constraints: Can be applied to multiple columns in a table and is declared independent of the column definition. Table constraint must be used when there is more than one column in a constraint
• Column Constraint: Stated as part of a column definition and applies only to that column.

Types of constraints in SQL Server:

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• CHECK
• NOT NULL

PRIMARY KEY (Primary Key)

a table typically has a primary key consisting of a unique column or a combination of columns to uniquely identify each row in the table. The PRIMARY KEY constraint is used to generate a primary key for the purpose of ensuring the integrity of the entity in the table.

Note: In a table, only one PRIMARY KEY can be created.

Two rows in a table cannot have the same primary key value, and a column that is a primary key cannot have a NULL value . Therefore, when a primary key constraint is added to existing table columns, SQL Server checks whether the rules for the primary key are followed. If the existing columns do not conform to the primary key rules, the constraint will not be added and an error will be returned.

Docs: https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-ver15

Syntax for adding primary key to column while adding table:

 CREATE TABLE <table_name> (Column_Name datatype PRIMARY KEY [,column_list])

For example:

 create database empDB GO use empDB GO create table EmpContactPhone (Employee_ID int primary key, MobileNumber bigint, serviceProvider varchar(30), LandlineNumber bigint)

After running the command successfully, the empDB database is created with the Employee_ID column as the primary key:

Syntax to add primary key to column when editing table:

 ALTER TABLE <table_name> ADD PRIMARY KEY (<column_name>)

For example:

 create database empDB GO use empDB GO create table EmpContactPhone (Employee_ID int, MobileNumber bigint, serviceProvider varchar(30), LandlineNumber bigint) use empDB GO alter table EmpContactPhone alter column Employee_ID int not null; GO alter table EmpContactPhone ADD PRIMARY KEY(Employee_ID) ;

UNIQUE (Unique)

Unique constraints are used to ensure that only unique values are entered in a column or set of columns. It allows developers to ensure that no duplicate values are entered. Primary keys are always unique. UNIQUE keys enforce entity integrity because once the constraints are applied, no two rows in the table can have the same values for the columns. The UNIQUE constraint allows null values. A table can have more than one UNIQUE constraint.

Syntax:

 create table <table_name> ([column_list,] <column_name> <data_type> UNIQUE [,column_list])

For example:

 create table EmpContactPhoneUnique (Employee_ID int, MobileNumber bigint unique, serviceProvider varchar(30), LandlineNumber bigint)

With the above syntax, when inserting data in column MobileNumber if there are duplicates other than , SQL will report an error, for example:

 insert into EmpContactPhoneUnique values (1,0782222567,'vinaphone','84') insert into EmpContactPhoneUnique values (2,0782222567,'vinaphone','84')

FOREIGN KEY (Foreign Key)

A foreign key in a table is a column that points to a primary key or a unique column in a table. Foreign key constraints are used to enforce referential integrity. A foreign key can be understood as a relationship between two tables to form a relational database schema.

Note: the foreign key of this table needs to be associated with the primary key of another table, otherwise the initialization statement will give an error

Docs: https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver15

The syntax for foreign keys is as follows:

 CREATE TABLE <table_name1>([column_list,]) <column_name> <datatype> FOREIGN KEY REFFERENCES <table_name> (<pk_column_name> [,column_list])

Explain:

• table_name: the name of the table to which the primary key column is referenced
• <pk_column_name>: primary key column name of the table

For example:

 create database empDB GO use empDB GO create table EmpContactPhone (Employee_ID int, MobileNumber bigint, serviceProvider varchar(30), LandlineNumber bigint) use empDB GO alter table EmpContactPhone alter column Employee_ID int not null; GO alter table EmpContactPhone ADD PRIMARY KEY(Employee_ID) ; create table EmpPhoneExpenses (Expense_ID int primary key identity, Employee_ID int FOREIGN KEY REFERENCES EmpContactPhone(Employee_ID),Amount bigint)

In the above example, we understand the Employee_ID column of the EmpContactPhone table has a foreign key and associate it with the Employee_ID column which is the primary key of the EmpContactPhone table.

CHECK

This constraint is used to check the limit of values that can be placed in the column. The CHECK constraint enforces data integrity.

For example, a check constraint could be introduced if the value entered for the column representing Voter Age must be greater than or equal to 18.

If the data entered for the column does not match the condition, the insert will fail.
The check constraint works by specifying a search condition, which can evaluate to TRUE, FALSE, or undefined. Values that evaluate to FALSE will be rejected. Multiple CHECK constraints can be specified for a single column. A single CHECK constraint can also be applied to multiple columns by creating it at the table level.

Syntax:

Docs: https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-check-constraints?view=sql-server-2017

For example:

 create table Employee (ID int primary key identity, Name nvarchar(30), Phone varchar(30), Age int check (Age > 18))

Next step, try to test insert data into employee table with data that violates the constraint:

 insert into Employee values (N'Toan Ngo Vinh','0911111111',15)

NOT NULL

The not null constraint ensures that a column value cannot be null. Used to ensure the integrity and logic of data, similar to the CHECK . constraint