Home » SQL

SQL Constraints

SQL | Constraints: In this tutorial, we are going to learn about the various constraints in SQL with its usages, syntaxes and query examples.
Submitted by Abhishek Goel, on April 10, 2020

SQL | Constraints

Constraints are the guidelines implemented on the information sections of a table. These are utilized to restrict the kind of information that can go into a table. This guarantees the precision and unwavering quality of the information in the database.

Constraints could be either on a segment level or a table level. The segment level constraints are applied uniquely to one segment, though the table level Constraints are applied to the entire table.

The following are probably the most usually utilized constraints accessible in SQL.

1. NOT NULL - Guarantees that a section can't have NULL worth.
2. DEFAULT - Gives a default incentive to segment when none is determined.
3. PRIMARY KEY - Interestingly distinguishes each column/record in a database table.
4. UNIQUE - Guarantees that all qualities in a section are unique.
5. FOREIGN KEY - Extraordinarily distinguishes a column/record in any of the given database tables.
6. CHECK - The CHECK requirement guarantees that all the qualities in a section fulfill certain conditions.
7. INDEX - Used to make and recover information from the database rapidly.

Constraints can be only be used either while creating a table (CREATE TABLE) or while altering any table (ALTER TABLE).

Syntax:

    CREATE TABLE table_name 
    (
	    column1 datatype Constraint,
	    column2 datatype Constraint,
	    column3 datatype Constraint,
       ....
    );

Dropping Constraints

Any constraint defined by the programmer can be dropped using the DROP CONSTRAINT option using the ALTER TABLE command.

Syntax:

    ALTER TABLE <table_name> DROP CONSTRAINT <column_name>

Integrity Constraints

Integrity constraints are utilized to guarantee the precision and consistency of the information in a social database. Information integrity is taken care of in a social database through the idea of referential integrity.

There are numerous sorts of integrity constraints that assume a job in Referential Integrity (RI). These constraints incorporate Primary Key, Foreign Key, Unique Constraints and different constraints which are referenced previously.

Now let us see all constraints in detail with examples.

1) NOT NULL

In the event that we determine a field in a table to be NOT NULL. At that point the field will never acknowledge invalid worth. That is, you will be not permitted to embed another column in the table without indicating any an incentive to this field.

Example:

CREATE TABLE Emp
(
id int NOT NULL,
name varchar(10) NOT NULL,
address varchar(20)
);

A table with named "Emp" will be created,

ID	NAME	ADDRESS

2) UNIQUE

This constraint serves to exceptionally recognize each line in the table. For example for a specific segment, all the lines ought to have extraordinary qualities. We can have more than one UNIQUE section in a table.

Example:

CREATE TABLE emp
(
ID int NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
INSERT INTO emp(ID,NAME,ADDRESS)
VALUES(1123,'Hari','S colony');
INSERT INTO emp(ID,NAME,ADDRESS)
VALUES(1123,'Sunny','S colony');

This command will give an error as the ID constrains needs to be unique instead, we have kept it same as the other.

3) PRIMARY KEY

Primary Key is a field that remarkably recognizes each line in the table. On the off chance that a field in a table as Primary key, at that point the field won't have the option to contain NULL qualities just as all the lines ought to have one of a kind qualities for this field. In this way, as such, we can say this is a blend of NOT NULL and UNIQUE limitations.

Example:

CREATE TABLE emp
(
ID int NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);

"emp" table will be created. We have taken some of the records in it to use the table in further constraints.

Table: emp

ID	NAME	ADDRESS
112	Hari	S colony
3211	Sunny	J vihar
443	Mona	T Apartments
3212	Kamini	F plot

Here we can clearly see that each ID is NOT NULL value and is unique. Also, id is a primary key.

4) FOREIGN KEY

Foreign Key is a field in a table which exceptionally distinguishes each column of another table. That is, this field focuses to the primary key of another table. This normally makes a sort of connection between the tables.

Table: emp1

ID	NAME	AGE
112	Hari	45
3211	Sunny	26
443	Mona	33
3212	Kamini	45

Table: emp2

AGE	DESIGNATION	SALARY
45	Clerk	35000
26	Manager	45000
33	Accountant	35000
45	Receptionist	25000

Query:

CREATE TABLE emp1
(
ID int NOT NULL,
NAME int NOT NULL,
AGE int,
PRIMARY KEY (ID),
FOREIGN KEY (AGE) REFERENCES emp2(AGE)
);

5) CHECK

Utilizing the CHECK requirement we can indicate a condition for a field, which ought to be fulfilled at the hour of entering esteems for this field.

For instance, the underneath inquiry makes a table emp1 and determines the condition for the field AGE as (AGE >= 18) That is, the client won't be permitted to enter any record in the table with AGE < 18.

Table: emp1

ID	NAME	AGE
112	Hari	45
3211	Sunny	26
443	Mona	33
3212	Kamini	45

Query:

CREATE TABLE emp1
(
id int NOT NULL,
name varchar(10) NOT NULL,
age int NOT NULL CHECK (AGE >= 18)
);

6) DEFAULT

This requirement is utilized to give a default an incentive to the fields. That is, if at the hour of entering new records in the table in the event that the client doesn't indicate any an incentive for these fields, at that point the default worth will be doled out to them.

For instance, the underneath question will make a table named emp1 and determine the default an incentive for the field AGE as 18.

CREATE TABLE emp1
(
ID int NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18
);