SQL Constraints : Introduction

• SQL Constraints are the “Limits or Rules” that the users can specify while creating the tables and inserting values in it. Constraints means “Restrictions” in data which ensures better performance of the databases.
• Also, SQL Constraints restricts the type of data that is to be inserted in the table of the database allowing better results while executing SQL queries.
• The SQL Constraints that can be used are listed and explained below.

SQL Constraints : Types

SQL Constraints : NOT NULL

• “NOT NULL” means, a field which must have a value if it is marked with a NOT NULL constraint. By default, any field in tables present in the database can hold NULL values too.
• But, after applying NOT NULL constraint, the marked field should have a value against it.

For example : Consider a student_details table. The “ROLL_No” and “Name” columns need to have a value against them as they cannot be left “Blank or NULL”. We can use the below query to add “NOT NULL” constraint to it.

Query : CREATE Table student_details(Roll_No Integer NOT NULL, Name varchar(10) NOT NULL, Marks Integer);

NOTE : Once a table is created, NOT NULL constraints cannot be added after that. It needs to be added while creating the tables.

SQL Constraints : UNIQUE

• “UNIQUE” SQL constraints provides the luxury to add multiple columns which can have unique values throughout. It can be related to the concept of primary key.
• The UNIQUE constraint if implemented ensures that the columns contains unique values. Let’s make clear it with an example.

For example : Consider a student_details table. We can add the UNIQUE constraint to the Roll_No column as all the values would be unique. This can be done by executing below query.

 

Query : CREATE Table student_details(Roll_No Integer NOT NULL UNIQUE, Name char(20) NOT NULL, Marks Integer);

SQL Constraints : PRIMARY KEY

• A “PRIMARY KEY” constraint is the one which allows users to uniquely identify every single record present in the table through a single attribute.
• A table can only have a single PRIMARY KEY constraint but can have multiple UNIQUE constraints.

For example : Consider a student_details table. We can only add a single PRIMARY KEY constraint to Roll_No. Also, Admission_No will be a unique value can be treated as either a UNIQUE value or as PRIMARY KEY. Both cannot be treated as PRIMARY KEY together.

Query : CREATE Table student_details(Roll_No Integer PRIMARY KEY NOT NULL, Admission_No varchar(10) UNIQUE NOT NULL, Name char(20) NOT NULL, Marks Integer);

SQL Contraints : Foreign KEY

• The “FOREIGN KEY” constraint is used to establish a link among two tables through a common attribute present in both the tables. Also, FOREIGN KEY refers to PRIMARY KEY of other table.

For example : Consider two tables student_details and student_relation. FOREIGN KEY constraint can be added as :

Query : CREATE Table student_details(Roll_No Integer PRIMARY KEY NOT NULL, Name char(10) NOT NULL, Address varchar(20), Age Integer);

And

Query : CREATE Table student_relation(Roll_No Integer UNIQUE, Marks Integer);

• Here, Roll_No of student_details table is PRIMARY KEY and will act as FOREIGN KEY for student_realtion table.

SQL Constraints : CHECK

• The “CHECK” constraint in SQL is used to add a condition to columns while creating the tables. It is done so as, whenever the values are inserted, the CHECK condition must be fulfilled first and then only the values are inserted in that columns.

For example : We can add a “CHECK” condition against the “Marks” column in student_details table of “Only values of Marks <= 100 will be accepted” while inserting the values in the table. Below is the query.

 

Query : CREATE Table student_details(Roll_No Integer PRIMARY KEY NOT NULL, Name Char(10) NOT NULL, Marks Integer CHECK(Marks<=100));

SQL Constraints : DEFAULT

• The “DEFAULT” constraints in SQL is used to add default values whenever required. It means, for a particular column having DEFAULT constraint , some DEFAULT values will be added as chosen by the user while creating the table.

For example : Consider the table student_details in which we can take the maximum marks column as DEFAULT constraint. It can be done by executing below query.

 

Query : CREATE Table student_details(Roll_No Integer PRIMARY KEY NOT NULL, Name char(10), Max_Marks Integer DEFAULT 100, Marks_Scored Integer);