SQL Constraints

Read this article to understand the Types of SQL constraints along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, we will discover the power of SQL constraints in database design and management. We will see the different types of constraints, such as primary key, foreign key, unique, and check constraints, and will learn how they ensure data integrity, enforce relationships, and validate data. We will get insights into their syntax, usage, and real-world examples, empowering us to create robust and reliable SQL databases.

In this post, I will provide you a short overview of SQL Constraints and some additional keywords with all its options. In addition, we will cover some questions which we receive from the audience. 

Let’s talk about the theory of Constraints and why we should use it.

Definition

Constraints are used when we have to apply any criteria while inserting the data into table, while we have to update or delete the data from table. Constraint will check the criteria first and then it will allow to perform the operation. Constraints help maintain data integrity and enforce business rules. Here are some commonly used SQL constraints along with their definitions and examples.

Reasons to Use

As it’s name suggest we can use the Constraints to apply the criteria while inserting, updating or deleting the data. Below are the some key areas where we need to use Constraints.

1. Maintain the unique records based on business rule.
2. Apply some basic checks for data quality.
3. Constraints ensure the integrity and consistency of data in a database. They enforce rules and restrictions on the data, preventing the insertion of invalid or inconsistent values. Constraints help maintain the accuracy and reliability of the data.
4. Constraints provide a way to validate data at the database level. By defining constraints, you can specify conditions that the data must meet before it can be inserted, updated, or deleted. This helps enforce business rules and prevent the storage of incorrect or inconsistent data.
5. Constraints such as foreign keys establish relationships between tables, enforcing referential integrity. They ensure that values in a column match the values in another table’s primary key or unique key. This maintains the consistency of relationships between tables and prevents orphaned or inconsistent data.
6. Constraints like primary keys and unique keys ensure that each row in a table is uniquely identified. They prevent duplicate entries and ensure that certain columns or combinations of columns have unique values. This is crucial for accurate identification and retrieval of specific rows in a table.

Here are some commonly used SQL constraints along with their definitions and examples.

1. PRIMARY KEY
2. FOREIGN KEY
3. UNIQUE
4. NOT NULL
5. CHECK
6. DEFAULT
7. AUTO_INCREMENT
8. INDEX
9. CASCADE
10. ON DELETE
11. ON UPDATE
12. CONSTRAINT

PRIMARY KEY

Ensures that each row in a table is uniquely identified. It enforces the uniqueness and non-nullability of a column or a combination of columns.

Syntax

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
...
);

Example 1

CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

In above example, we have a table called “Customers” with a primary key constraint on the “customer_id” column. So while inserting the data into Customers table, it will allow only the unique and not null value into customer_id column.

FOREIGN KEY

It ensures that values in a column match the values in another table’s primary key or unique key. It will not allow to insert the records which primary keys are not present into parent table.

Syntax

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
.......,
FOREIGN KEY (column_n) REFERENCES Main_table(primary_key_of_main_table)
);

Example 2

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Above example will create Orders table with order_id will be unique and primary key while customer_id will be foreign key. Order table will allow to insert those orders which customers are present into Customers table only. If we tries to insert the records with any customer_id which is not belongs to Customers table then record will be rejected.

UNIQUE

Ensures that the values in a column or a combination of columns are unique across the table. Primary key will not allow null values.

Syntax

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
column3 data_type UNIQUE,
...
);

Example 3

CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
employee_email VARCHAR(100) UNIQUE
);

In above example, we have Employees table with employee_id will be primary key while employee_email will be unique. So employee_id and employee_email will always unique while NULL value will be accepted in employee_email but not in employee_id.

NOT NULL

Specifies that a column cannot contain NULL values.

Syntax

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
column3 data_type NOT NULL,
...
);

Example 4

CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2)
);

In above example, We have Products table with product_id primary key. The product name should not be null so we have added NOT NULL constraints for product_name column.

CHECK

Enforces a condition that must be true for the inserted or updated values in a column. Check constraint is applied when we have to apply any certain business rule on column data before inserting or updating.

Syntax

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
column3 data_type CHECK (condition),
...
);

Example 5

CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
age INT CHECK (age > 18)
);

In above example, Check constraint will allow only to insert the records whose age is greater than 18 only.

DEFAULT

Specifies a default value for a column if no value is provided during an insertion.

Syntax

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
column3 data_type DEFAULT value,
...
);

Example 6

CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
salary DECIMAL(10,2) DEFAULT 50000
);

In above example, Default constraint will assign the 50000 default salary if new record having null value for salary column.

CONSTRAINT

Defines a constraint that combines multiple rules or conditions.

Example 7 with Syntax

CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100),
age INT,
CONSTRAINT check_age CHECK (age BETWEEN 18 AND 25)
);

CASCADE

Specifies that changes made to a referenced primary key column are cascaded to the foreign key column.

Example 8 with syntax

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE
);
CREATE TABLE OrderItems (
item_id INT PRIMARY KEY,
order_id INT,
item_name VARCHAR(100),
CONSTRAINT fk_order_id FOREIGN KEY (order_id)
REFERENCES Orders(order_id)
ON DELETE CASCADE
);

ON DELETE

The constraint specifies the action to take when deleting a referenced row.

ON UPDATE

The constraint specifies the action to take when updating a referenced row.

I hope all above example clears the concept of Constraints and it’s use.

Linking

Different types of constraints, such as primary key, foreign key, unique, and check constraints are used to ensure data integrity, enforce relationships, and validate data. You can refer all the interview questions and Quiz. Please refer all the links for understanding the Constraint in depth.

Interview Questions

Basic SQL Constraint Interview questions

1. What is a constraint in SQL?
2. What is the purpose of using constraints in a database?
3. Name some commonly used constraints in SQL.
4. How do you define a primary key constraint in SQL?
5. How does a foreign key constraint maintain referential integrity?
6. What is the difference between a unique constraint and a primary key constraint?
7. Can a column have multiple constraints defined on it?
8. How do you specify a default value for a column in SQL?
9. What does the CASCADE option do in a foreign key constraint?

Intermediate SQL Constraint Interview questions

1. Can you have a foreign key constraint referencing multiple columns in another table? If so, how would you define such a constraint?
2. How do you create a composite primary key constraint on multiple columns in a table?
3. What is the purpose of the CHECK constraint? Provide an example scenario where the CHECK constraint would be useful.
4. Can you create a foreign key constraint that references multiple tables? If so, explain how you would define such a constraint and when it would be applicable.
5. How do you create an index on a column? What is the benefit of using indexes in terms of constraints and query performance?
6. What is a self-referential foreign key constraint? Provide an example scenario where a self-referential foreign key constraint would be applicable.

Advanced SQL Constraint Interview questions

1. Describe the concept of constraint validation order in SQL. How does the order of constraint evaluation impact the outcome and performance of database operations?

Quiz

Click on this link to solve few MCQ questions on Constraint.

Happy offline learning… Lets meet in next session.