Constraints

Constraints defines rules or restrictions that are enforced on data columns in a table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.

Types of Constraints

There are several types of constraints in SQL. They are:

NOT NULL: Ensures that a column cannot have a NULL value.
UNIQUE: Ensures that all values in a column are different.
PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
FOREIGN KEY: Uniquely identifies a row/record in another table.
CHECK: Ensures that all values in a column satisfy a specific condition.
DEFAULT: Sets a default value for a column when no value is specified.
INDEX: Used to create and retrieve data from the database very quickly.

Examples

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. For example, if we have a table student where phonenumber is one of the columns, we can use the UNIQUE constraint to ensure that all phone numbers are unique.

Click to see the details

Let's create a table student with a phonenumber column and add a UNIQUE constraint to it.

table.sql

CREATE TABLE IF NOT EXISTS student (
phonenumber INT UNIQUE
);

💡

This will ensure that all phone numbers in the student table are unique.

Expected Output for wrong query:

example_query.sql

-- Inserting values in college
INSERT INTO student (phonenumber) VALUES
(1323),(1233344),(1323);

Output:

UNIQUE Error

Correct way:

example_query.sql

-- Inserting values in college
INSERT INTO student (phonenumber) VALUES
(1323),(1233344);

Output:

UNIQUE Error Fix

NOT NULL Constraint

NOT NULL constraint ensures that a column cannot have a NULL value.

Click to see the details

Let's create a table student with a name and age column. We will add a NOT NULL constraint to the age column.

table.sql

CREATE TABLE IF NOT EXISTS student (
name VARCHAR(50),
age INT NOT NULL
);

The NOT NULL constraint ensures that a column cannot have a NULL value. In the above example, the age column cannot have a NULL value.

Example:

example_query.sql

INSERT INTO student (name) VALUES ('Swayam');

Output:

NOT NULL Error

Correct Code:

example_query.sql

INSERT INTO student (name, age) VALUES ('Swayam', 22);

Output:

NOT NULL Error Fix

💡

The NOT NULL constraint ensures that the age column cannot have a NULL value.

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition.

Click to see the details

Let's create a table student with a age column. We will add a CHECK constraint to the age column to ensure that the age is greater than 18.

table.sql

CREATE TABLE IF NOT EXISTS student (
name VARCHAR(50),
age INT CHECK (age > 18)
);

💡

The CHECK constraint ensures that all values in the age column are greater than 18.

Example:

example_query.sql

INSERT INTO student (name, age) VALUES ('Swayam', 16);

Output:

CHECK Error

Correct Code:

example_query.sql

INSERT INTO student (name, age) VALUES ('Swayam', 18);

Output:

CHECK Error Fix

DEFAULT Constraint

The DEFAULT constraint sets a default value for a column when no value is specified.

Click to see the details

Let's create a table student with a school column. We will add a DEFAULT constraint to the school column to set a default value.

table.sql

CREATE TABLE IF NOT EXISTS student (
name VARCHAR(50),
school VARCHAR(50) DEFAULT 'GHRCEM',
age INT NOT NULL CHECK (age >=18)
);

💡

The DEFAULT constraint sets the default value of the school column to 'GHRCEM'.

Example:

example_query.sql

INSERT INTO student (name, age)
VALUES ('Swayam', 22);

Output:

DEFAULT Error

PRIMARY KEY Constraint

Primary key constraint uniquely identifies each row in a table.

Click to see the details

💡

Unique + Not Null constraints are automatically added to the primary key column.

table.sql

CREATE TABLE IF NOT EXISTS student (
name VARCHAR(50),
school VARCHAR(50) DEFAULT 'GHRCEM',
age INT NOT NULL CHECK (age >=18),
rollnumber INT PRIMARY KEY
);

💡

The PRIMARY KEY constraint ensures that the rollnumber column uniquely identifies each row in the student table.

Inserting values into the table:

example_query.sql

-- Inserting values in TABLE student
INSERT INTO student (name,age, rollnumber)
VALUES
("Swayam",18,1),
("Ravi",21,2);

FOREIGN KEY Constraint

The FOREIGN KEY constraint uniquely identifies a row/record in another table.

Click to see the details

Let's create two tables student and student2. The student2 table has a rollnumber column, which is a foreign key that references the rollnumber column in the student table.

foreign.sql

CREATE TABLE student2 (
name VARCHAR(50),
rollnumber INT,
FOREIGN KEY (rollnumber) REFERENCES student(rollnumber)
);

Create a relationship between two tables. The student table has a rollnumber column, which is a foreign key that references the rollnumber column in the marks table.

6. Keys 8. Update Query