How to Add SQL Constraints? - Examples and Usage

Explore the SQL constraints for data control and security. See how to apply constraints effectively in your SQL database and prevent errors. | ProjectPro

Recipe Objective - How to Add SQL Constraints? - Examples and Usage  

When working with databases, ensuring data integrity is paramount. SQL constraints are essential tools that help maintain data accuracy and consistency within your database tables. This recipe will guide you through the journey into what SQL constraints are, the various types of SQL constraints, and practical examples and usage scenarios to help you understand how to add constraints in SQL.

What are Constraints in SQL Server? 

SQL constraints are rules and restrictions applied to columns or tables to maintain data integrity. They ensure that data entered into a database follows predefined criteria, preventing incorrect, inconsistent, or inappropriate data from being stored. Constraints are an integral part of creating robust and reliable databases.

Types of Constraints in SQL - SQL Constraints List

Constraints in SQL are essential rules that define the structure and integrity of a database. There are several types of constraints, each serving a specific purpose to ensure data accuracy and consistency.  

  1. Primary Key Constraint

A primary key is a unique identifier for each row in a table. It enforces uniqueness and ensures that each row has a distinct value for the primary key column. To add a primary key constraint in SQL, use the PRIMARY KEY keyword.

  1. Foreign Key Constraint

A foreign key establishes a relationship between two tables, ensuring referential integrity. It links a column in one table to the primary key in another table. To add a foreign key constraint in SQL, use the FOREIGN KEY keyword.

  1. Default Constraint

A default constraint specifies a default value for a column if no value is provided when inserting data. To add a default constraint in SQL, use the DEFAULT keyword.

  1. Check Constraint

A check constraint defines a condition that must be satisfied for data to be entered into a column. It can be used to ensure values meet certain criteria. To add a check constraint in SQL, use the CHECK keyword.

  1. Not Null Constraint

A not null constraint ensures that a column cannot contain NULL values. It enforces the presence of a value in the column. To add a not null constraint in SQL, use the NOT NULL keyword.

  1. Unique Constraint

A unique constraint guarantees that values in a column are unique, except for NULL values. It ensures that there are no duplicate entries in that column. To add a unique constraint in SQL, use the UNIQUE keyword.

SQL Project for Data Analysis using Oracle Database-Part 1

How to Add Constraints in SQL Server? 

SQL constraints are indispensable for maintaining the integrity and consistency of your database. Here is the process of adding various types of constraints to your SQL tables.

How to Add PRIMARY KEY Constraint in SQL? 

Each row in a table is uniquely identified by the values of a column or combination of columns. The primary key refers to this column or combination of columns. The column that acts as a primary key cannot have NULL values and must contain UNIQUE values.

Let’s say we want to create an employee table with the columns employee_id, employee_name, employee_age, and employee_salary. The employee_id should act as the primary key and the employee_name should be NOT_NULL. We can add these constraints as follows –

Syntax:
Create table table_name(
Column_name datatype PRIMARY KEY,
...);

Code:

CREATE TABLE employee(
employee_id numeric PRIMARY KEY,
employee_name varchar(100) NOT NULL,
employee_age numeric,
employee_salary integer);

How to Add Primary Key in SQL

You can add the PRIMARY KEY constraint with the help of the CONSTRAINT statement.

Syntax:
CREATE TABLE table_name(
column_name1 datatype,
column_name2 datatype,
...
CONSTRAINT constraint_name PRIMARY KEY (column_name)
);

Primary keys can consist of one column or more. A primary key that consists of more than one column is known as the composite primary key. If your primary key consists of more than one column, it must act as a table constraint.

Let us create an employee table, same as above, but with employee_id and employee_name acting as the primary key.

Code:

CREATE TABLE employee(
employee_id numeric,
employee_name varchar(100),
employee_age numeric,
employee_salary integer,
CONSTRAINT c_eid_ename PRIMARY KEY (employee_id, employee_name)
);

How to Add Primary Key Constraint in SQL

If we have already created a table, for example, our customers table, we can use the “ALTER” statement to apply constraints as follows –

Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
Or
ALTER TABLE table_name ADD constraint_name PRIMARY KEY (column_name);

Code:

ALTER TABLE customers ADD CONSTRAINT c_id PRIMARY KEY (customer_id);

How to Add Primary Key Constraint using ALTER

In case, there arises a need to drop the PRIMARY KEY constraint, you can do so as follows –

Syntax:

ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint;

Or in the case of MySQL, you can use -

ALTER TABLE table_name DROP PRIMARY KEY;

Code:

ALTER TABLE employee DROP CONSTRAINT c_eid_ename;
-- or for mysql
ALTER TABLE customers DROP PRIMARY KEY;

How to Drop Primary Key Constraint in SQL

How to Add FOREIGN KEY Constraint in SQL? 

A foreign key is a column (or set of columns) that establishes a relationship between data in two tables. The primary key column (or columns) of the first table is referenced by the column (or columns) of the second table in a foreign key reference. The foreign key is the column (or columns) of the second table.

Syntax: Create table table_name1(
Column_name datatype PRIMARY KEY,
...
FOREIGN KEY (foreign_key) REFERENCES table_name2(primary_key));

Code:

CREATE TABLE orders
(order_id NUMERIC PRIMARY KEY,
customer_id NUMERIC,
amount INTEGER
FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

How to Add Foreign Key in SQL

If we have already created a table, for example, our customers table, we can use the “ALTER” statement to apply constraints as follows –

Syntax:
ALTER TABLE table2name
ADD FOREIGN KEY (column_name) REFERENCES table1name(primary_key_column_name) ;

Code:

ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ;

How to Add Foreign Key Constraint using ALTER

If there arises a need to drop this constraint, you can do by using the following syntax –

Syntax:
-- MySQL
ALTER TABLE table_name
DROP FOREIGN KEY FK_name;

-- SQL
ALTER TABLE table_name
DROP CONSTRAINT FK_name;

Code:

-- mysql
ALTER TABLE Orders
DROP FOREIGN KEY customer_id;

-- sql
ALTER TABLE Orders
DROP CONSTRAINT customer_id;

How to DROP Foreign Key Constraint in SQL

How to Add DEFAULT Constraints in SQL? 

You can explicitly decide a default value for your column. By default the default is NULL but you can set this to something else. If no values are inserted for the particular column, its default value will be added.

Syntax:
Create table table_name(
Column_name datatype DEFAULT default_value,
...);

Let’s say we want to create an employee table with the columns employee_id, employee_name, employee_age, and employee_salary. The employee_id should act as the primary key and the employee_name should be NOT_NULL. The default salary should be set to 40,000. We can add these constraints as follows –

Code:

CREATE TABLE employee(
employee_id numeric PRIMARY KEY,
employee_name varchar(100) NOT NULL,
employee_age numeric,
employee_salary integer DEFAULT 40000);

How to Add Constraint in SQL

If we have already created a table, for example, our customers table, we can use the “ALTER” statement to apply constraints as follows –

Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
DEFAULT default_value FOR column_name;

OR for MySQL

ALTER TABLE table_name
ALTER column_name SET DEFAULT default_value;

Code:

--sql
ALTER TABLE employee
ADD CONSTRAINT c_sal
DEFAULT 40000 FOR employee_salary;

-- or for mysql
ALTER TABLE employee
ALTER employee_salary SET DEFAULT 40000;

In case, there arises a need to drop the PRIMARY KEY constraint, you can do so as follows –

Syntax:
ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint;
Or in the case of MySQL, you can use -
ALTER TABLE table_name DROP PRIMARY KEY;

Code:

--sql
ALTER TABLE employee
ALTER COLUMN employee_salary DROP DEFAULT;

-- or for mysql
ALTER TABLE employee
ALTER employee_salary DROP DEFAULT;

How to Add CHECK Constraint in SQL? 

If you want the data of a column to be in a particular range (eg: Age>13), you can make use of the CHECK constraint. It will add the values only if the given condition is met.

Syntax:
Create table table_name(
Column_name datatype CHECK (check_condition),
...);

Let’s say we want to create an employee table with the columns employee_id, employee_name, employee_age, and employee_salary. The employee_id should act as the primary key and the employee_name should be NOT_NULL. The employee_age should be greater than or equal to 21. We can add these constraints as follows –

Code:

CREATE TABLE employee(
employee_id numeric PRIMARY KEY,
employee_name varchar(100) NOT NULL,
employee_age numeric CHECK (employee_age>=21),
employee_salary integer);

Alternatively, you can also make use of the following syntax –

Syntax:
Create table table_name(
Column_name1 datatype,
Column_name2 datatype,
...
CONSTRAINT constraint_name CHECK (check_condition)
);

Code:

CREATE TABLE employee(
employee_id numeric PRIMARY KEY,
employee_name varchar(100) NOT NULL,
employee_age numeric CHECK (employee_age>=21),
employee_salary integer,
CONSTRAINT chk_age CHECK (employee_age>=21));

Now, if you want to add a CHECK constraint to a preexisting table, you can do so with the help of ALTER statement.

Syntax:
ALTER TABLE table_name ADD CHECK (check_condition);
Or if you want to be able to name CHECK, use-
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (check_condition);

Code:

ALTER TABLE employee ADD CHECK (employee_age>=21);
--or
ALTER TABLE employee
ADD CONSTRAINT chk_age CHECK (employee_age>=21);

If at any given point you want to drop the CHECK constraint, you can do so using the following query-

Syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
For MySQL-
ALTER TABLE table_name DROP CHECK constraint_name;

Code:

-- sql
ALTER TABLE employee DROP CONSTRAINT chk_age;

--mysql
ALTER TABLE employee DROP CHECK chk_age;

Hadoop Project to Perform Hive Analytics using SQL and Scala

How to Add NOT NULL Constraint in SQL? 

The NOT NULL constraint when applied to a column ensures that no NULL value is entered into a column.

Syntax:
Create table table_name(
Column_name datatype NOT NULL,
...);

Let’s say we want to create an employee table with the columns employee_id, employee_name, employee_age, employee_salary and we want that the values of both employee_id and employee_name should not be null. We can do this by adding the NOT NULL constraint as follows –

Code:
CREATE TABLE employee(
employee_id numeric NOT NULL,
employee_name varchar(100) NOT NULL,
employee_age numeric,
employee_salary integer);

If we have already created a table, for example, our customers table, we can use the “ALTER” statement to apply constraints as follows –

Syntax:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Code:

ALTER TABLE customers MODIFY customer_id numeric NOT NULL;

How to Add UNIQUE Constraint in SQL? 

You may want some of your columns to have unique content. Eg: the employee id has to be unique and so does the email id. To make sure that all the values of a column are unique, you can add the UNIQUE constraint to your column.

Let’s say we want to create an employee table with the columns employee_id, employee_name, employee_age, employee_salary and we want that the values of both employee_id and employee_name should not be null. We also want the employee_id to be unique. We can add these constraints as follows –

Syntax:
Create table table_name(
Column_name datatype UNIQUE,
...);

Code:

CREATE TABLE employee(
employee_id numeric NOT NULL UNIQUE,
employee_name varchar(100) NOT NULL,
employee_age numeric,
employee_salary integer);

Alternatively, we can also add the UNIQUE constraint with the help of the CONSTRAINT statement as follows –

Syntax:
CREATE TABLE table_name(
column_name1 datatype,
column_name2 datatype,
...
CONSTRAINT constraint_name UNIQUE (column_name)
);

Code:

CREATE TABLE employee(
employee_id numeric NOT NULL,
employee_name varchar(100) NOT NULL,
employee_age numeric,
employee_salary integer,
CONSTRAINT c_eid UNIQUE (employee_id)
);

If we have already created a table, for example, our customers table, we can use the “ALTER” statement to apply constraints as follows –

Syntax:
ALTER TABLE table_name MODIFY column_name datatype UNIQUE;

Code:
ALTER TABLE customers MODIFY customer_id numeric NOT NULL UNIQUE;

How to Add INDEX Constraint in SQL? 

Indexes are used to get data from a database faster than would otherwise be possible. The indexes are hidden from the users and are only used for faster searches and queries. You can make use of the “CREATE INDEX” statement to create indexes on tables.

Syntax:
CREATE INDEX index_name
ON table_name (column_name1, column_name2, ...);
Or to create unique index –
CREATE UNIQUE INDEX index_name
ON table_name (column_name1, column_name2, ...);

To add an index to the employee table follow the given query –

Code:

-- creating index
CREATE INDEX i_emp1
ON employee (employee_id,employee_salary);

--creating unique index
CREATE UNIQUE INDEX i_emp2
ON employee (employee_name, employee_age);

In case, there arises a need to drop the INDEX constraint, you can do so as follows –

Syntax:
-- SQL
DROP INDEX table_name.index_name;
-- mysql
ALTER TABLE table_name DROP INDEX index_name;

Code:

-- sql
DROP INDEX employee.i_emp1;
-- mysql
ALTER TABLE employee DROP INDEX i_emp2;

Learn More About SQL Constraints with ProjectPro! 

Learning how to use SQL constraints is really important if you work with databases. These constraints help keep data accurate and make your database work better and more securely. This recipe has covered various examples and shown you how to use them in real situations. But here's the key: just knowing the theory isn't enough. To get really good at using SQL constraints and other database stuff, you need hands-on practice. That's where ProjectPro comes in. They have more than 270 project solutions in data analysis, data science and big data that you can learn from and work on. It's a great way to get practical experience and boost your skills. So, check out ProjectPro Repository today to learn more about SQL constraints and boost your data analysis skills. 

What Users are saying..

profile image

Ed Godalle

Director Data Analytics at EY / EY Tech
linkedin profile url

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills... Read More

Relevant Projects

Linear Regression Model Project in Python for Beginners Part 2
Machine Learning Linear Regression Project for Beginners in Python to Build a Multiple Linear Regression Model on Soccer Player Dataset.

Demand prediction of driver availability using multistep time series analysis
In this supervised learning machine learning project, you will predict the availability of a driver in a specific area by using multi step time series analysis.

Machine Learning Project to Forecast Rossmann Store Sales
In this machine learning project you will work on creating a robust prediction model of Rossmann's daily sales using store, promotion, and competitor data.

Build a Customer Churn Prediction Model using Decision Trees
Develop a customer churn prediction model using decision tree machine learning algorithms and data science on streaming service data.

Build an End-to-End AWS SageMaker Classification Model
MLOps on AWS SageMaker -Learn to Build an End-to-End Classification Model on SageMaker to predict a patient’s cause of death.

Text Classification with Transformers-RoBERTa and XLNet Model
In this machine learning project, you will learn how to load, fine tune and evaluate various transformer models for text classification tasks.

OpenCV Project to Master Advanced Computer Vision Concepts
In this OpenCV project, you will learn to implement advanced computer vision concepts and algorithms in OpenCV library using Python.

NLP Project for Multi Class Text Classification using BERT Model
In this NLP Project, you will learn how to build a multi-class text classification model using using the pre-trained BERT model.

Recommender System Machine Learning Project for Beginners-2
Recommender System Machine Learning Project for Beginners Part 2- Learn how to build a recommender system for market basket analysis using association rule mining.

PyTorch Project to Build a LSTM Text Classification Model
In this PyTorch Project you will learn how to build an LSTM Text Classification model for Classifying the Reviews of an App .