Table of contents
1.
Introduction
2.
What are SQL constraints?
3.
Types of SQL Constraints
3.1.
Difference between Column-level Constraint and Table-level Constraint
3.2.
The NOT NULL Constraint 
3.3.
The Foreign KEY Constraint
3.4.
The PRIMARY KEY SQL Constraint
3.4.1.
Foreign Key V/S Primary Key
3.5.
The UNIQUE SQL Constraint 
3.6.
The DEFAULT Constraint
3.7.
The Check SQL Constraint 
4.
Frequently Asked Questions
4.1.
What are SQL Constraints?
4.2.
What are the types of SQL constraints?
4.3.
Enlist the six most common SQL constraints.
4.4.
Which SQL constraint accepts null values?
4.5.
Can there be more than one primary Key?
5.
Conclusion
Last Updated: Mar 27, 2024
Medium

Constraints in SQL Server

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Hello Ninjas, Do you find the SQL constraints confusing? Do you still have trouble understanding the primary Key and foreign Key? Do not worry, for you have coding ninjas here to solve your doubts. In this blog, we will discuss the SQL constraints intensely.

constraints in SQL

What are SQL constraints?

SQL constraints are a batch of rules that limit the data that can be inserted in the table. The constraints serve as a condition in which, if satisfied, the data is inserted in the table. Otherwise, the insertion is adjourned. They oversee the data's accuracy, integrity, and reliability in the tables. In an SQL server, the constraints can be added at the column level or independently at the table level. 

Types of SQL Constraints

On the basis of group, SQL constraints are classified into:

  • Column-level constraints in SQL server: Column-level constraints are the rules specified for a column or a group of columns. They are used to ensure data integrity in the database.
     
  • Table-level constraints in SQL server: Table-level constraints in SQL are applied to the entire table and limit the range of values entered in the database. 

 

On the basis of column-level and table-level constraints in SQL server, SQL constraints are classified into six typical types:

  1. NOT NULL constraint: This constraint makes sure that there are no null data values in the column
     
  2. FOREIGN KEY constraint: The Foreign key constraint averts those actions that may crash the links between tables.
     
  3. PRIMARY KEY constraint: The constraint is used to identify table rows uniquely.
     
  4. UNIQUE constraint: This ensures that all the data in the column are distinct and unique.
     
  5. DEFAULT constraint: The default constraints put default data in the column if it is empty.
     
  6. CHECK constraint: This constraint is used to verify if the data in the column satisfy a specific condition.
     
SQL constraints

Now, let us dive deeper into the topic and understand the SQL constraints in a better way.

Difference between Column-level Constraint and Table-level Constraint

The difference between column-level and table-level constraints are as follows:

Column-level Constraint

Table-level Constraint

Column-level SQL constraints can be applied to a single column or multiple columns. They restrict the data values that can be entered or inserted into the table. Table-level SQL constraints are defined for the entire SQL table, preventing any unauthorized data from being inserted.
Column-level constraints can be stated at the time of creating the tables. Table-level constraints can be stated after table creation with the help of the ALTER command.
A column-level constraint in SQL can be expressed at the table level. A table-level constraint in SQL cannot be defined at the column-level.
The scope of a column-level constraint is only to the column at which the constraint is specified. The scope of a table-level constraint is at the entire table.

The NOT NULL Constraint 

The NOT NULL constraint is used in a database to verify that the columns do not contain any non-empty values. The columns in the SQL database are defaulted and assigned to NULL values. If null values are added to the table, it is treated as invalid data. The NOT NULL constraint can limit the null values from being inserted into the table. That means every cell in the database where the NOT NULL constraint is specified should contain a value. Hence Updation and Insertion should not involve empty values.

The NOT NULL constraint can be specified either while creating the table or after table creation using the ALTER command.

NOT NULL constraint on create table

CREATE Table Employees(
EmpId int NOT NULL,
Name varchar(255) NOT NULL
);
DESC Employees


Output:

NOT NULL constraint code output

With the NOT NULL constraint specified in the above SQL statements, the columns EmpId and Name of the table employees cannot accept any NULL values.

For example:

INSERT INTO Employees VALUES(002, NULL);


Output:

NOT NULL Code output

NOT NULL constraint on Alter table

ALTER Table Employees CHANGE EmpId EmpId int NOT NULL;
DESC Employees

 

Output:

not null constraints code output
INSERT INTO Employees VALUES(NULL, 'Hanks');  


Output:

NOT NULL Constraint code output

The Foreign KEY Constraint

The FOREIGN KEY constraint establishes a link between two tables. It is used to refer to a column of the child table containing the foreign Key to the column of the parent table containing the primary Key. Thus a foreign key is also known as a referencing key. That means the insertion of only those data is possible which are related to the parent table. Any unauthorized or unwanted data is not allowed in the database. Thus it can also be said that the foreign Key prevents actions that may break the link between two tables. 

Unlike the primary Key, which should be unique for a table, there can be multiple foreign keys referring to multiple parent tables. Foreign keys also allow NULL values in a table. Foreign Key is used to enforce data and referential integrity in a database. 

The PRIMARY KEY constraint can be specified either while creating the table or after table creation using the ALTER command.

FOREIGN KEY constraint on Create table

CREATE Table Employees(
EmpId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL,
Age int
);
CREATE Table Department(
DepId int NOT NULL PRIMARY KEY,
EmpId int NOT NULL,
Salary int,
FOREIGN KEY (EmpId) REFERENCES Employees (EmpId)
);
DESC Department;


The EmpId in the Departments table refers to the EmpId in the Employees table. The EmpId in the Employees table is the Primary Key, whereas that in the Departments table is the Foreign Key.

Output:

foreign key constraint

FOREIGN KEY constraint on Alter table

ALTER TABLE Department 
ADD FOREIGN KEY (EmpId) REFERENCES Employees (EmpId);
DESC Department;


Output:

Foreign key constraint

The PRIMARY KEY SQL Constraint

The PRIMARY key constraint in SQL uniquely recognizes each row in a table. It can be visualized as a combination of the SQL UNIQUE and NOT NULL constraints where the columns of the primary Key constraint in the SQL server are unique and cannot have any null values. The primary key constraint is used to implement data integrity. The PRIMARY KEY constraint can be specified either while creating the table or after table creation using the ALTER command.

PRIMARY KEY constraint on create table

CREATE Table Employees(
EmpId int NOT NULL,
Name varchar(255) NOT NULL,
PRIMARY KEY (EmpId)
);


While creating the Employees' table with the above SQL statements, a primary key on EmpId is created.

Output:

Primary key
INSERT INTO Employees VALUES (1,'Robert'),  (2, 'Joseph');    
INSERT INTO Employees VALUES (1,'Brayan');


Output:

Primary Constraint code output

PRIMARY KEY constraint on Alter table

ALTER Table Employees
Add PRIMARY KEY (EmpId);
DESC Employees

 

Output:

primary key constraint
INSERT INTO Employees VALUES (1,'Robert'),  (2, 'Joseph');    
INSERT INTO Employees VALUES (1,'Brayan');


Output:

Primary Constraint code output

Foreign Key V/S Primary Key

Foreign Key

Primary Key

It is used to link tables together. The primary Key uniquely identifies a column or a record in the database.
NULL values are allowed in foreign Key. NULL values are not permitted in the primary Key.
There can be several foreign keys for a specific table. There is only one primary Key for one table.
Foreign keys can contain repeated values. It is composed of unique and NOT NULL values.

The UNIQUE SQL Constraint 

The UNIQUE SQL Constraint ensures no duplicity in the data values of a single column or a group of columns that are not a part of the primary key constraint. With the help of this SQL constraint the database can have unique values. It should be noted that the constraint is similar to the primary Key and can include one null data in the table if the NOT NULL constraint is absent for the column. Also, a table can have multiple Unique keys.

The UNIQUE constraint can be specified either while creating the table or after table creation using the ALTER command. An error message is returned if a UNIQUE SQL constraint is set for a column with twin values.   

Unique constraint on create table

CREATE Table Employees(
EmpId int NOT NULL UNIQUE,
Name varchar(255) NOT NULL
);


The above SQL constraint ensures that the EmpId is unique for every employee when the Employees table is created.

Output:

unique constraint

Now, we try to insert two tuples in the database.

INSERT INTO Employees VALUES(001, 'Smith'), (001, 'Roy'); 


Output:

Unique Constraint code output


Unique constraint on Alter table

ALTER Table Employees
Add UNIQUE (EmpId);
DESC Employees;


The above SQL constraint ensures that the EmpId is unique for every employee when the Employees table is already created.

Output:

unique constraint

Now, we try to insert two tuples in the database with same EmpId.

INSERT INTO Employees VALUES(1, 'Smith'), (001, 'Roy'); 


Output:

Unique Constraint code output

The DEFAULT Constraint

The DEFAULT constraint adds default values in a column if no values are stated. The default value can be either NULL, a system function, or a constant value. The default value gets inserted automatically if no data is specified. When new data is entered by the user, the default value gets overruled by the new value. It maintains the integrity of the database.

The UNIQUE constraint can be specified either while creating the table or after table creation using the ALTER command.

Default constraint on create table

CREATE Table Employees(
EmpId int NOT NULL,
Name varchar(255) NOT NULL,
City varchar(255) DEFAULT 'New York'
);
DESC Employees;


Output:

default constraint

 

Next 3 tuples are inserted in the database. The first two tuples contain EmpId, Name and City where as the last tuple contains only EmpId and Name. The default value for the city automatically gets inserted in the respective column.

INSERT INTO Employees VALUES (1,'Robert','Florida'),   
(2, 'Joseph','California');  
INSERT INTO Employees(EmpId, Name) VALUES (1,'Brayan');
Select * from Employees;


Output:

Default Constraint code output


Default constraint on Alter table

ALTER Table Employees
ALTER City SET DEFAULT' New York';
DESC Employees;

 

Output:

default constraint


Next 3 tuples are inserted in the database. The first two tuples contain EmpId, Name and City where as the last tuple contains only EmpId and Name. The default value for the city automatically gets inserted in the respective column.

INSERT INTO Employees VALUES (1,'Robert','Florida'),   
(2, 'Joseph','California');  
INSERT INTO Employees(EmpId, Name) VALUES (1,'Brayan');
Select * from Employees;


Output:

Default Constraint code output

The Check SQL Constraint 

The Check constraint in SQL databases enforces data integrity by restricting the range of data values in the table column. It checks the validity of each data in the table from a logical expression. Here every data value inserted in the table follows a specific rule. Thus the database does not contain any unauthorized value. 

The check constraint can be applied either to a single column at the column level or a group of columns at the table level.

Check constraint on create a table

CREATE Table Employee(
EmpId int NOT NULL,
Name varchar(255) NOT NULL,
City varchar(255) DEFAULT 'New York'
Age int,
CHECK (Age>=23) 
);
DESC Employees;

 

Output:

check constraint
INSERT INTO Employees(EmpId, Name, Age) VALUES (1,'Brayan', 20);
Select * from Employees;


Output:

Check Constraint code output

The above SQL statements ensure that the age of the employees inserted at the time of table creation is greater than equal to the age of 23.

Check constraint on Alter table

ALTER Table Employees
Add CHECK (Age>=23);
DESC Employees


Output:

check constraint
INSERT INTO Employees(EmpId, Name, Age) VALUES (1,'Brayan', 20);
Select * from Employees;


Output:

Check Constraint code output

Also Read - TCL Commands In SQL

Frequently Asked Questions

What are SQL Constraints?

Ans. Constraints in SQL server are a set of rules that restrict the data that can be inserted into the database. They help maintain the data accuracy, integrity, and reliability in the SQL tables.

What are the types of SQL constraints?

Ans. SQL constraints are of two types, Column level SQL constraints that are applied to single columns or a group of columns of an SQL table and table level SQL constraints that are applied to the entire SQL table.

Enlist the six most common SQL constraints.

Ans. The six most common SQL constraints are NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT, and CHECK constraint. These SQL constraints are responsisible for maintaining the integrity and accuracy of the database.

Which SQL constraint accepts null values?

Ans. Unlike the PRIMARY KEY constraint, the UNIQUE constraint allows one null value in the specified column. The records in the unique Key should be unique and hence allows one null value.

Can there be more than one primary Key?

Ans. Primary Keys are unique values that refer to the records of the entire database. Hence each table can have only one primary Key. The can be no repetition of values in a primary key of a table. The primary Key is used to implement data integrity in a database.

Conclusion

Congratulations on finishing this article. We have discussed the definition of SQL constraints along with the six typical constraints: the NOT NULL, FOREIGN KEY, PRIMARY KEY, UNIQUE, DEFAULT, and CHECK SQL constraints with their respective code examples.

We hope this blog has helped you enhance your knowledge of SQL constraints in Java. Keep learning! We recommend you go through some of our other SQL articles: 

  1. Basic SQL
  2. Top 100 SQL problems
  3. SQL Interview Questions


Refer to our Guided Path to upskill yourself in DSACompetitive ProgrammingJavaScriptSystem Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio!

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. For placement preparations, you must look at the problemsinterview experiences, and interview bundles.

We wish you Good Luck! 

Happy Learning!

Live masterclass