SQL Constraints
Constraints in SQL are the rules or conditions that specify what type of data can be stored or cannot be stored in the table. It means before storing the data into the columns of a table, we are checking for the rules or conditions specified, and how we specify the rules or conditions, is by using Constraints. If the condition applied by Constraint holds true while inserting/updating/deleting data, only then the data will be inserted/updated/deleted into/from the table and if the data, which we are inserting/updating/deleting violates any existing constraint, it will not perform the operation and will return an error.
CONSTRAINTS limit the type of data that can be inserted, updated, or
deleted.
CREATE
Constraints in SQL can be specified when we are creating the table with
CREATE TABLE Statement
ALTER
Constraints in SQL can also be specified after the table has been
created with the ALTER TABLE Statement.
Constraint of SQL
The following are the constraints available in the SQL:
• NOT NULL:
By using NOT NULL
constraint we can ensure that we cannot store a NULL value. Columns of the
table cannot remain empty where the NOT NULL constraint is used. So, there must
remain a value.
We can store 0 as 0 is a value
and NULL means empty.
CREATE TABLE Student
(
ID int NOT NULL
);
• UNIQUE:
By using the UNIQUE
constraint we ensure that only unique values can be stored in the column. We
cannot store any duplicate value in any row of a column. Hence, all values in a
column with UNIQUE constraint are different and unique.
The UNIQUE constraint can be used
on multiple columns of a table.
CREATE TABLE Student
(
ID int UNIQUE
);
• PRIMARY KEY:
A primary key is a
combination of UNIQUE and NOT NULL constraints. It means that the column with a
PRIMARY KEY constraint will always contain unique values and will not contain
null values. UNIQUE constraint and NOT NULL constraint together form a PRIMARY
KEY constraint.
CREATE TABLE Student
(
RollNo int,
PRIMARY KEY(ID)
);
• FOREIGN KEY:
When there are two
columns, in two different tables one table takes reference from the other table
with the help of a column that is present in both tables, and in one table that
column acts as the primary key. So the same column of another table will act as
FOREIGN KEY.
A Foreign Key is used for
referential integrity and Prevents actions that would destroy links between
tables
CREATE TABLE Course
RollNo int,
RollNo int references
Student(RollNo)
);
• CHECK:
With the CHECK constraint,
you can specify the conditions that should be followed while inserting the data
in a column. When you apply the CHECK constraint with a condition on a column
and ten insert data, the value will first get checked whether it holds the
condition true or not and the value will only get inserted only if the
condition is true.
For example: If you have a column named as Age in which you want to
allow users to only enter the age above 23, here, you can use the CHECK
constraint with a condition that Age should be greater than 23.
Now, if the user enters ae above
23 only tat it will et saved otherwise not.
CREATE TABLE EMPLOYEES(
AGE INT CHECK(AGE>=24)
);
• DEFAULT:
The DEFAULT constraint is
used when you want to specify a default value for a column, in case, the user
has not entered any value. So, when the user has not specified any value to be
inserted in a column with DEFAULT constraint then the default value which was
specified with DEFAULT constraint will be inserted into that column.
• CREATE INDEX:
The CREATE INDEX
constraint is used to create an index on the table. Indexes help the user to
create and retrieve data very quickly and also help speed up the searching
speed but are not visible to the users.
HOW TO DROP SQL CONSTAINTS???
SQL constraint can be dropped by
using the ALTER TABLE command with the DROP CONSTRAINT option. An example of
dropping the PRIMARY KEY constraint from the table EMPLOYEES is as below:
ALTER TABLE EMPLOYEES DROP CONSTRAINT PRIMARY KEY;
If you don't want to drop the
constraint permanently you can also disable the constraint and enable them
later whenever you want.