Learn All SQL Constraints | UNIQUE, PRIMARY KEY, FOREIGN KEY

Summary: You will learn in this article, all SQL Constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT etc.

What is SQL Constraints?

SQL Constraints are the rules which can apply to the type of data in a table. That is, we can specify the limit on the type of data that can be stored in a specific column in a table using SQL constraints.

In another word we can say:

SQL Constraints are the predefined set of rules and restrictions applied on the tables or columns for limited or restricting unauthorized values to be inserted into the tables. Furthermore, they are responsible for ensuring the column’s data accuracy, integrity, & reliability inside the table. Constraints in SQL also tell that the data will be inserted into the table when the inserted data satisfies the SQL constraint rule. Furthermore, otherwise, the insert operation will be terminated when if the inserted data violates the defined constraint.

Types of SQL Constraints

There are two types of constraints in SQL Server:

1. Table Level Constraints:

These constraints apply to the complete table that limits the types of data that can be entered into the table. Furthermore, its definitions are specified after creating the table using the ALTER SQL statement.

2. Column Level Constraints:

These constraints in SQL are applied to the single or multiple columns to limit the types of data that can be entered into the column. Furthermore, its definition is specified while creating the tables.

Which Constraints Are Used In SQL Server?

The Constraints in SQL are used to make sure that the integrity of data is maintained in the SQL database. There are the following most common constraints used in the SQL that we will describe with examples:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

I) NOT NULL Constraint

Columns in SQL Server by default it will store NULL values. Furthermore, we can restrict NULL value from being inserted into a given column by using a SQL NOT NULL constraint. Therefore, if we try to insert the NULL value in that column, then it is considered an invalid value.

It means that we must provide the valid non-null value in the table while using the INSERT or UPDATE operations as the column will always have data. We can apply NOT NULL constraints either during the creation of the table or after creating the table using the SQL ALTER statement.

The given below the following example will apply NOT NULL constraint in the column when the table is created:

CREATE TABLE Orders (  
    Order_ID int NOT NULL,  
    Product_ID int NOT NULL,  
    Product_Name varchar(255)  
);

CREATE TABLE Orders (

Order_ID int NOT NULL,

Product_ID int NOT NULL,

Product_Name varchar(255)

);

After that, we will insert few values into the Order table to see how this SQL constraint works.

INSERT INTO Orders VALUES(11, 201, 'Intex TV');    
INSERT INTO Orders VALUES(12, 116, 'Samsung Tv');
INSERT INTO Orders VALUES(13, 211, 'Xiaomi Mi TV');    
INSERT INTO Orders VALUES(14, 77, 'Videocon TV');
INSERT INTO Orders VALUES(15, NULL, 'LG TV');

INSERT INTO Orders VALUES(11, 201, 'Intex TV');

INSERT INTO Orders VALUES(12, 116, 'Samsung Tv');

INSERT INTO Orders VALUES(13, 211, 'Xiaomi Mi TV');

INSERT INTO Orders VALUES(14, 77, 'Videocon TV');

INSERT INTO Orders VALUES(15, NULL, 'LG TV');

Explanation:

When executing the above SQL insert statement will return the given below output. but you can notice here, that the first to fourth INSERT query executes correctly, but the 5th statement fails and gives an error that says column 'Product_ID' does not allow nulls.
sql constraints: NOT NULL Constraint
When we created a table “Order”, then we can notice that there is no constraint applied with the Product_Name column. Thus, it will insert the default value NULL. Furthermore, if we want to restrict the NULL value into this column, then we can use the given below statement:

SQL ALTER TABLE

ALTER TABLE Orders ALTER COLUMN Product_Name varchar(80) NOT NULL;

1	ALTER TABLE Orders ALTER COLUMN Product_Name varchar(80) NOT NULL;

SQL Server allows us to create a NOT NULL constraint in the table without the execution of a SQL statement. we will try this by using the management studio by first right-clicking on the specified table & choosing the design option. Furthermore, Check all the columns in the “Allow Nulls” option that stores the null value in the design window.
Allow Nulls value by design window

II) Unique Constraint

The SQL UNIQUE constraint restricts one or a lot of columns to contain unique values within a table.
Although both a UNIQUE constraint in SQL and a PRIMARY KEY constraint in SQL enforce uniqueness, use a UNIQUE constraint rather than a PRIMARY KEY constraint after you need to enforce the uniqueness of a column or combination of columns, that’s not the primary key.
The given below following SQL statement creates a table named customers and specifies the phone column as unique. meaning this field doesn’t permit duplicate values.

Example of Unique Constraint

CREATE TABLE Customers (
    customers_id INT NOT NULL PRIMARY KEY,
    customers_name VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

CREATE TABLE Customers (

customers_id INT NOT NULL PRIMARY KEY,

customers_name VARCHAR(30) NOT NULL,

birth_date DATE,

phone VARCHAR(15) NOT NULL UNIQUE

);

Important Note: Multiple UNIQUE constraints in SQL can be defined on a table, whereas only one SQL PRIMARY KEY constraint can be defined on a table. Furthermore, also, unlike PRIMARY KEY constraints, the SQL UNIQUE constraints allow NULL values.

III) PRIMARY KEY Constraint

The SQL PRIMARY KEY constraint can be defined as it identifies the column or set of columns that have values that uniquely determine a row in a table. Therefore, no two rows in a table can have identical primary key values. Furthermore, you can’t enter the NULL value in a primary key column.
The given below following SQL statement creates a table named customers and specifies the customers_id column as the primary key. which means this field doesn’t allow NULL or duplicate values.

Example of PRIMARY KEY Constraint

CREATE TABLE Customers (
    customers_id INT NOT NULL PRIMARY KEY,
    customers_name VARCHAR(30) NOT NULL,
    birth_date DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

CREATE TABLE Customers (

customers_id INT NOT NULL PRIMARY KEY,

customers_name VARCHAR(30) NOT NULL,

birth_date DATE,

phone VARCHAR(15) NOT NULL UNIQUE

);

Important Note: The SQL primary key typically consists of one(1) column in a table, however, more than one column will comprise the primary key, e.g. either the customer’s email address or assigned identification number is the logical primary key for a customer’s table.

IV) FOREIGN KEY Constraint

A foreign key (FK Key) in SQL is a column or combination of columns that are used to establish & enforce a relationship between the data in two tables.

The following is given below diagram showing the connection or relationship between the employees and departments table. Furthermore, If you verify it fastidiously, you’ll notice that the dept_id column of the employees' table matches the primary key column of the departments' table. Therefore, the dept_id column of the employees' table is the foreign key to the departments' table.

sql constraints: SQL foreign-key-relationship-diagram

Furthermore, in SQL Server, you’ll be to create a foreign key by defining a FOREIGN KEY constraint when you create a table as follow. The given below statement establishes a foreign key on the dept_id column of the employees’ table that references the dept_id column of the departments’ table.

Example of FOREIGN KEY Constraint

CREATE TABLE employees (
    emp_id INT NOT NULL PRIMARY KEY,
    emp_name VARCHAR(35) NOT NULL,
    hire_date DATE NOT NULL,
    salary INT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE employees (

emp_id INT NOT NULL PRIMARY KEY,

emp_name VARCHAR(35) NOT NULL,

hire_date DATE NOT NULL,

salary INT,

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

);

Important Note: FOREIGN KEY Constraint is used to established the relationship between the data in two tables.

V) CHECK Constraint

The CHECK constraint in SQL is utilized to restrict the values that can be placed during a column.

Therefore, for illustration, the range of values for a salary column can be limited by creating a CHECK constraint in SQL that allows values only from 1,000 to 3,000. This prevents salaries from being entered on the far side of the regular salary range. let’s see the given below example-

Example of CHECK Constraint

CREATE TABLE employees (
    emp_id INT NOT NULL PRIMARY KEY,
    emp_name VARCHAR(35) NOT NULL,
    hire_date DATE NOT NULL,
    salary INT NOT NULL CHECK (salary >= 1000 AND salary <= 3000),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE employees (

emp_id INT NOT NULL PRIMARY KEY,

emp_name VARCHAR(35) NOT NULL,

hire_date DATE NOT NULL,

salary INT NOT NULL CHECK (salary >= 1000 AND salary <= 3000),

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

);

Important Note: MySQL doesn’t support SQL check constraints. The CHECK clause is parsed however but ignored by all storage engines of MySQL.

VI) DEFAULT Constraint

The DEFAULT constraint in SQL is defined as it specifies the default value for the columns.
A column default is some value that will be inserted in the column by the database engine when an SQL INSERT statement doesn’t explicitly assign a particular value.
The given below following SQL statement creates a default for the country column.

Example of DEFAULT Constraint

CREATE TABLE persons (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(35) NOT NULL,
    birth_date DATE,
    phone VARCHAR(10) NOT NULL UNIQUE,
    country VARCHAR(20) NOT NULL DEFAULT 'USA'
);

CREATE TABLE persons (

id INT NOT NULL PRIMARY KEY,

name VARCHAR(35) NOT NULL,

birth_date DATE,

phone VARCHAR(10) NOT NULL UNIQUE,

country VARCHAR(20) NOT NULL DEFAULT 'USA'

);

Important Note: If you define a table column as NOT NULL, however, assign the column a default value, then in the SQL INSERT statement you don’t need to explicitly assign a value for that column in order to insert a new row in the table.

Type of Data constraints

Input/Output constraints: These constraints determine the speed at which data are inserted or extracted from the database table. For illustration Primary key, Foreign key constraints.
Business Rule constraints: These rules are applied to data prior to (first) to the data being inserted into the table columns. For example Unique, Not NULL, Default constraints.

ADD SQL CONSTRAINTS (ALTER TABLE)

Yes, you can add constraints to the existing table. But in this way when you add a new constraint, the oracle check any existing data violate the constraint rules or not. Furthermore, if not violate constraint added successfully otherwise you have to manually update data to prevent constraint violation.

Syntax:

ALTER TABLE table_name
    ADD CONSTRAINT_NAME (column_name);

1 2	ALTER TABLE table_name ADD CONSTRAINT_NAME (column_name);

DROP SQL CONSTRAINTS (ALTER TABLE)

We can say that yes, you can drop the existing table constraint using the SQL ALTER TABLE statement.

Syntax:

ALTER TABLE table_name
    DROP constraint_name column_name;

1 2	ALTER TABLE table_name DROP constraint_name column_name;

Conclusion:

You have learned in this article, What is SQL Constraints, Types of SQL Constraints, Type of Data constraints, ADD SQL CONSTRAINTS (ALTER TABLE), DROP SQL CONSTRAINTS (ALTER TABLE) etc. I hope you will enjoy it!

Learn All SQL Constraints | UNIQUE, PRIMARY KEY, FOREIGN KEY

What is SQL Constraints?

Types of SQL Constraints

1. Table Level Constraints:

2. Column Level Constraints:

Which Constraints Are Used In SQL Server?

I) NOT NULL Constraint

The given below the following example will apply NOT NULL constraint in the column when the table is created:

Explanation:

SQL ALTER TABLE

II) Unique Constraint

Example of Unique Constraint

III) PRIMARY KEY Constraint

Example of PRIMARY KEY Constraint

IV) FOREIGN KEY Constraint

Example of FOREIGN KEY Constraint

V) CHECK Constraint

Example of CHECK Constraint

VI) DEFAULT Constraint

Example of DEFAULT Constraint

Type of Data constraints

ADD SQL CONSTRAINTS (ALTER TABLE)

Syntax:

DROP SQL CONSTRAINTS (ALTER TABLE)

Syntax:

Conclusion:

Related Posts

About Author

admin