What is SQL Constraint? All Constraints Explained!

SQL Constraints are used to set rules for data in table. Constraints can be apply to column or whole table at the time of creation or after creation of table. It is very important to understand all constraints before going deep into SQL.

List of Constraints:

• Unique
• Check
• Default
• Not Null
• Index
• Primary key
• Foreign key

Unique:

Unique constraint means all the values inserted in column should be unique. If you wanted to Enter any duplicate value then it will throw an error and does not allow you to insert it.

Syntax:

Create table tablename

(column1 int,

Column2 Varchar(15),

Column3 Varchar(10),

Unique (column1));

Example:

Create table students

(Roll_no int,

Name Varchar(15),

Division Varchar(10),

Unique (Roll_no));

  

Check:

Check constraint used to check a particular condition throughout a column. If you try to enter value not supported condition then it will give an error and does not allow you to insert.

Syntax:

Create table tablename

(column1 int NOT NULL,

Column2 Varchar(15),

Column3 Varchar(10)

Check (column1<=10));

Example:

Create table students

(Roll_no int NOT NULL,

Name Varchar(15),

Division Varchar(10)

Check (Roll_no<=10));

For version 8.0 of MySQL check constraint will accept insert value even if it does not fulfill condition.

Not Null:

Not Null constraint means column should not contain any Null value.

Syntax:

Create table tablename

(column1 int NOT NULL,

Column2 Varchar(15),

Column3 Varchar(10));

Example:

Create table students

(Roll_no int NOT NULL,

Name Varchar(15),

Division Varchar(10));

Default:

Default constraint used to insert a default value in column if no other value is mentioned.

Syntax:

Create table tablename

(column1 int NOT NULL,

Column2 Varchar(15),

Column3 Varchar(10) default ‘value1’);

Example:

Create table students

(Roll_no int NOT NULL,

Name Varchar(15),

Division Varchar(10) default ‘C’);

Index:

It is used to ceate index on table. With index it will be easy to understand and use data. User can not see the index but they are generated.

Syntax:

Create index indexname

On table (Column1, Column2, Column3);

Example:

Create index indexname

On table (Roll_no, Name, Division);

Primary Key:

Primary Key is a Candidate key. It does not have any null value. And it should have all unique values. Primary key is something with help of which we can uniquely identify all records. It can be a single column or more than one column. You don’t need to specify or create primary key every time. SQL automatically get to know which is primary key in table. But if you wanted to create primary key then,

Syntax:

Create table tablename

(column1 int,

Column2 Varchar(15),

Column3 Varchar(10),

Primary key (column1));

Example:

Create table students

(Roll_no int,

Name Varchar(15),

Division Varchar(10),

Primary key (Roll_no));

Foreign Key:

Foreign key is identical to primary key, it is reference of primary key but into some other table. It is used to link two tables.

Syntax:

Create table tablename

(column1 int,

Column2 Varchar(15),

Column3 Varchar(10),

Foreign key (column1) references tablename (column1));

Example:

Create table subjects

(Roll_no int,

English Varchar(15),

Maths Varchar(10),

Foreign key (Roll_no) references Students (Roll_no));

you’ll understand it clearly when we will go through Joins…