Constraints in SQL: An In-depth Tutorial with Examples

What Are Constraints in SQL?

SQL constraints are rules that you can imply on the data in a table. It allows you to restrict only specific data that meets the regulations to go to a table. To put it simply, only if the data meets the constraint’s rules, the insert operation will be successful, or else it will be aborted.

Constraints in SQL helps to maintain the accuracy, integrity, and reliability of a table’s data. It can create them at a column or table level. If you declare constraints at the column level, it will apply them to a single column. On the other hand, if you declare them at the table level, it will implement them in more than one column. You can create constraints in SQL while creating a table using the CREATE TABLE command or later using the ALTER TABLE command. If you make a constraint with the ALTER TABLE command, the creation will only be successful if all the existing data meets the constraint rules.

How to Create Constraints in SQL?

As mentioned, you can create constraints in SQL using the CREATE TABLE command while creating a new table or ALTER TABLE command while altering an existing table. The basic syntax of creating an SQL constraint using the CREATE TABLE command is:

CREATE TABLE table_name(

column_name1 data_type(size) constraint_name,

column_name2 data_type(size) constraint_name,

….

);

In the above syntax:

• table_name: Name of the table you want to create
• column_name: Name of the column you want to create
• data_type: Data type of the value you want to add to the column
• size: Maximum size (length) of the column
• constraint_name: Name of the constraint you want to create and implement

You can also create a constraint in SQL using the ALTER TABLE command through the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name data_type(size) constraint_name

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Types of Constraints in SQL

Now that you know the basic syntax of creating a constraint in SQL, let’s move to the types of constraints. There are a total of six SQL constraints available. In this section, you will look deeply into all the constraints in SQL and create them.

• Not Null Constraint in SQL

By default, SQL tables can accept null values. But if you want to restrict columns from taking null values, you can use the NOT NULL constraint in SQL. Using this constraint will simply prohibit anyone from inserting a new row in the table while keeping the field for this column null. This is useful in scenarios where you need the user to put input for something necessarily. For instance, if you want to check age restriction, the user must input age. Thus, you can use the NOT NULL constraint to the age column.

In the example below, you will create a table Student1 with three columns Name, Age, and ID. While making the table, you must keep the Name and ID column as NOT NULL. You will then use the insert command to input data and see if it accepts null values.

Example

-- creating a table

CREATE TABLE Student1(

  ID int NOT NULL,

  Name varchar(25) NOT NULL,

  Age int

);

-- inserting some values

INSERT INTO Student1 VALUES (1, 'Aakash', 21);

INSERT INTO Student1 VALUES (2, null, null);

Output:

As you can see in the output, the query returned an error saying that Student1.Name cannot accept null values. Now let’s provide a value to the Name column of the second INSERT INTO command and see whether it runs the command successfully.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Example

-- creating a table

CREATE TABLE Student1(

  ID int NOT NULL,

  Name varchar(25) NOT NULL,

  Age int

);

-- inserting some values

INSERT INTO Student1 VALUES (1, 'Aakash', 21);

INSERT INTO Student1 VALUES (2, 'George', null);

Output:

As you can see, even the field for the age column was left null, the query executed successfully as you provided values for NOT NULL columns. Now, fetch values from this table using the SQL SELECT command.

-- fetching values

SELECT * FROM Student1;

Output:

• UNIQUE Constraint in SQL

As the name gives out, the UNIQUE constraint prohibits any duplicate values in a column. If a column has a UNIQUE constraint, it will not allow any similar row entry for that field. You can declare more than one column as UNIQUE in a table. In the example below, you will take the same Student1 table and add an Email_Id column and keep it unique. You will also have to insert a duplicate value in the Email_Id column and see the output.

CREATE TABLE Student1(

  ID int NOT NULL,

  Name varchar(25) NOT NULL,

  Age int,

  Email_Id NVARCHAR(50) UNIQUE

);

INSERT INTO Student1 VALUES (1, 'Aakash', 21, 'ak@12');

INSERT INTO Student1 VALUES (2, 'George', null, 'go@45');

SELECT * FROM Student1;

-- trying to insert duplicate values

INSERT INTO Student1 VALUES (3, 'Rahul', 21, 'go@45');

Output:

• PRIMARY KEY Constraint in SQL

The PRIMARY KEY constraint is a combination of both NOT NULL and UNIQUE constraints. This means that when you define a column with PRIMARY KEY, it will not accept any null or duplicate values. You will create the Student2 table in the code below and determine the ID column with the PRIMARY KEY constraint. You will then try to insert the same and null values in different rows and look at the output.

CREATE TABLE Student2(

  Name varchar(25) NOT NULL,

  ID int PRIMARY KEY,

  Age int,

  Email_Id NVARCHAR(50) UNIQUE

);

INSERT INTO Student2 VALUES ('Aakash', 1, 21, 'ak@12');

INSERT INTO Student2 VALUES ('George', 2, NULL, 'go@45');

INSERT INTO Student2 VALUES ('Rahul', 1, 21, 'rh@67');

INSERT INTO Student2 VALUES (NULL, 3, 23, 'mr@89');

SELECT * FROM Student2;

Output:

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

• FOREIGN KEY Constraint in SQL

The FOREIGN KEY constraint in SQL is usually used to build relationships between tables in a database. It handles foreign affairs by connecting to the PRIMARY KEY values of another table. But to create this connection, you need to pass a command, which is:

FOREIGN KEY (column_name_first_table) REFERENCES (column_name_second_table)

Now, you will use this command in the example below to create a FOREIGN KEY constraint in SQL.

CREATE TABLE ORDERS(

    O_ID int PRIMARY KEY,

    ORDER_NO int UNIQUE,

    C_ID int,

    FOREIGN KEY (C_ID) REFERENCES CUSTOMERS(C_ID)

);

INSERT INTO ORDERS VALUES (1, 2212, 3);

INSERT INTO ORDERS VALUES (2, 2015, 1);

INSERT INTO ORDERS VALUES (3, 1983, 1);

INSERT INTO ORDERS VALUES (4, 1502, 2);

CREATE TABLE CUSTOMERS(

    C_ID int PRIMARY KEY,

    NAME VARCHAR(25) NOT NULL,

    CITY VARCHAR(20)

);

INSERT INTO CUSTOMERS VALUES (1, 'Aakash', 'MUMBAI');

INSERT INTO CUSTOMERS VALUES (2, 'George', 'DELHI');

INSERT INTO CUSTOMERS VALUES (3, 'Rahul', 'AHMEDABAD');

SELECT * FROM ORDERS;

SELECT * FROM CUSTOMERS;

Output:

As you can see in the two tables, the C_ID column in the PRIMARY KEY for the CUSTOMERS tables and FOREIGN KEY for the ORDERS table. It will help create a mapping and relationship between both the tables.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

• CHECK Constraint in SQL

The CHECK constraint allows you to specify a condition that needs to be met to insert data into a table successfully. A simple example will be to create a table for the voting list. It is evident that a person needs to be above 18 years of age to qualify for voting. Thus, with the CHECK constraint’s help, the administrator will only enter the list of people above 18 years of age. Let’s see this example in action.

CREATE TABLE VOTING_LIST(

    ID int PRIMARY KEY,

    Name NVARCHAR(50) NOT NULL,

    Age int NOT NULL CHECK (AGE>=18)

);

INSERT INTO VOTING_LIST VALUES (1, 'Aakash', 19);

INSERT INTO VOTING_LIST VALUES (2, 'Rahul', 23);

INSERT INTO VOTING_LIST VALUES (3, 'George', 16);

INSERT INTO VOTING_LIST VALUES (4, 'Mario', 35);

SELECT * FROM VOTING_LIST;

Output:

• DEFAULT Constraint in SQL

The DEFAULT constraint in SQL is used to assign a default value to the left empty fields. Thus, if you neither want a user to enter empty fields nor want to implement the NOT NULL constraint, DEFAULT constraint can come in handy. It is also helpful to provide a default value to a field if set to NOT NULL. This will prevent the constraint error, as the field will be given the default value and will not remain NULL.

CREATE TABLE VOTING_LIST(

    ID int PRIMARY KEY,

    Name NVARCHAR(50) NOT NULL,

    Age int NOT NULL DEFAULT 18

);

INSERT INTO VOTING_LIST VALUES (1, 'Aakash', 19);

INSERT INTO VOTING_LIST VALUES (2, 'Rahul', 33);

INSERT INTO VOTING_LIST VALUES (3, 'George', 16);

-- inserting values by specifying column list to see the DEFAULT constraint in action

INSERT INTO VOTING_LIST (ID, Name) VALUES (4, 'Mario');

SELECT * FROM VOTING_LIST;

Output:

As you can see in the output, when you didn’t provide any value for the Age column for the last insertion, the system gave it the default value, which in this case is 18.

• CREATE INDEX Constraint in SQL

The CREATE INDEX constraint in SQL helps in retrieving the results more quickly. It is like a book’s index. Like the book’s index helps us find a chapter soon, the table’s index allows the system to retrieve results quickly. The CREATE INDEX constraint is not visible or of any use to the user; it is only helpful to the backend or the system. The syntax for creating an index is:

CREATE INDEX index_name

ON table_name (col1, col2, col3, ...)

Let’s look at the below example to understand better how to create and use the CREATE INDEX constraint.

CREATE TABLE Student3(

  Name varchar(25) NOT NULL,

  ID int PRIMARY KEY,

  Age int,

  Email_Id NVARCHAR(50) UNIQUE

);

INSERT INTO Student3 VALUES ('Aakash', 1, 21, 'ak@12');

INSERT INTO Student3 VALUES ('George', 2, NULL, 'go@45');

INSERT INTO Student3 VALUES ('Rahul', 3, 21, 'rh@67');

INSERT INTO Student3 VALUES ('Mario', 4, 23, 'mr@89');

CREATE INDEX Student3_Index

ON Student3 (Name, ID, Age, Email_Id);

SELECT * FROM Student3;

Output:

As you can see in the output, it didn’t affect creating the table, as it is only helpful for the system to retrieve results quickly.

Conclusion

In this article, you learned about constraints in SQL. Constraints will help you control what data goes into your database. They are essential for creating meaningful databases, as there are always some restrictions and rules that you would want to impose on the data being entered into the database. If you wish to learn more about constraints then check out our next tutorial on Integrity Constraints.

Understanding and using constraints in your database is a fundamental concept. But if you are looking to build a career in the database management field, it is vital to understand the advanced concepts along with the basics. You can opt for Simplilearn’s Post Graduate Program In Full Stack Web Development to get acquainted with those advanced concepts.

If you have any questions for us, leave them in the comments section of this article. Our experts will get back to you on the same, ASAP!