Constraints in SQL
Constraints
In SQL:
Constraints
are the rules that must be followed to enter data in the table. The constraint is
just like a check that is applied to ingoing and outgoing of data in the table.
It is used to check that which types of data would be entered in the table. The
data that violates the constraint can’t be entered in the table. A constraint can
be defined in two ways table level and column level. Table level
constraint can be applied to the whole table and column level constraint can be
applied to the specific column.
How
to apply constraint:
We
can apply constraint at the time of creating a table using CREATE TABLE keyword.
If we want to change the constraint after applying it then we can perform
updation through ALTER TABLE statement.
Syntax
of Constraints at Column Level:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint
);
Syntax
of Constraints at Table Level:
CREATE TABLE table_name (
column1 datatype
column2 datatype
column3 datatype
CONSTRAINT constraint_Name constraint_Keyword(Column_Name));
There
are different constraints available in SQL
that are:
·
NOT NULL Constraint:
This constraint
restricts the user not to store a null value in a column. That is, if a column is
specified as NOT NULL then the user will not be able to store null
in this specific column anymore. so, if this
constraint is applied the user has to enter some value in a column.
Example:
UNIQUE Constraint:
when this
constraint is specified with
a column restricts that all the values in the column must be unique. So, the values in any row of a column must not be repeated. This constraint
also ensures that the duplicate values can’t be entered in the column. This
constraint can be applied at the table and column levels.
·
Example of UNIQUE Constraint At Column
level:
CREATE TABLE Student (
Std_ID int UNIQUE NOT NULL,
Std_Name varchar(255) NOT NULL,
Subject varchar(255),
Marks int );
Example
of UNIQUE Constraint At Table level
CREATE TABLE Student (
Std_ID int NOT NULL,
Std_Name varchar (255) NOT NULL,
Subject varchar (255),
Marks int
CONSTRAINT Std_Student UNIQUE (Std_ID)
);
Std_Student IS A CONSTRAINT NAME.
· PRIMARY KEY Constraint:
A primary key is a field that
is used to uniquely identify each row in a table. This constraint is used to specify a
Column as primary key in a table. A column
on which this PRIMARY KEY Constraint
is applied can’t contain a duplicate value as well as a NULL value. only one primary key can be defined in the table if two columns are selected as primary
key then it is called Composite Primary
key.
·
Example of PRIMARY KEY Constraint At Column
level:
CREATE TABLE Student (
Std_ID int NOT NULL PRIMARY KEY,
Std_Name varchar (255) NOT NULL,
Subject varchar (255),
Marks int);
Example
of PRIMARY KEY Constraint At Table level
CREATE TABLE Student (
Std_ID int NOT NULL,
Std_Name varchar (255) NOT NULL,
Subject varchar (255),
Marks int
CONSTRAINT PK_Student PRIMARY KEY (Std_ID, Subject)
);
PK_Student IS A CONSTRAINT NAME that the user
can define of his own choice.
· FOREIGN KEY Constraint:
A Foreign key is a field that is used to uniquely identify each row in another table. And
this constraint· is used to specify a column as a Foreign key in
a table. The foreign key is used to create a link between the data of the two tables.
For applying the Foreign key there must be a common column in both tables on the
basis of which link is created. one table act as a parent table that is the table
from which data is taken as reference and the other table acts like a child table
in which the referenced data is used. The child table can have duplicate values.
LET SUPPOSE THERE ARE TWO TABLES Student AND TEACHER. We will use
Std_Id as Foreign key in TEACHER table.
·
Example of FOREIGN KEY Constraint At column
level:
CREATE TABLE Teacher (
T_ID int NOT NULL Primary key,
Teacher_Name varchar (255) NOT NULL,
Std_ID int FOREIGN KEY REFERENCES Student(Std_ID));
·
Example of FOREIGN KEY Constraint At
table level :
CREATE TABLE Teacher (
T_ID int NOT NULL Primary key,
Teacher_Name varchar (255) NOT NULL,
CONSTRAINT FK_Std FOREIGN KEY (Std_ID)
REFERENCES Student(Std_ID)
);
This constraint is used to validate the values of a column to satisfy
a particular condition. In this
way, it helps to ensure
that the value stored in a column meets specific criteria
specified in a condition. It
is used to apply a test on data values.
·
Example of CHECK Constraint:
Std_ID int NOT NULL PRIMARY KEY,
Std_Name varchar (255) NOT NULL,
Subject varchar (255),
Marks int CHECK (Marks>=40));
In the above example check constraint will allow the value of
Marks Column must be greater than 40.
·
Example
of CHECK Constraint on multiple columns:
CREATE TABLE Student (
Std_ID int NOT NULL PRIMARY KEY,
Std_Name varchar (255) NOT NULL,
Subject varchar (255),
CONSTRAINT CHK_Std_marks CHECK (Marks>=40 AND Subject='CS'));
Adding or Dropping Constraint:
Sql provides the facility to enable or disable the constraint after applying it. This can be done using ALTER TABLE command.
Syntax of Modifying Constraint:
Alter table Table_Name
Add Constraint_Name constraint_type(column_name);
Examples:
Modifying UNIQUE Constraint:
ALTER TABLE Student
ADD UNIQUE (Std_ID);
Multiple columns:
ALTER TABLE Student
ADD CONSTRAINT UNQ_Std UNIQUE (Std_ID,Subject);
Modifying PRIMARY KEY Constraint:
ALTER TABLE Student
ADD PRIMARY KEY (Std_ID);
Multiple columns:
ALTER TABLE Student
ADD CONSTRAINT Pk_Std Primary key (Std_ID,Subject);
Modifying FOREIGN KEY Constraint:
ALTER TABLE TEACHER
ADD FOREIGN KEY (Std_ID) REFERENCES Student(Std_ID);
Multiple columns:
ALTER TABLE TEACHER
ADD CONSTRAINT FK_id
FOREIGN KEY (Std_ID) REFERENCES Student(Std_ID);
Modifying CHECK Constraint:
ALTER TABLE Student
ADD CHECK (Marks>=40);
Multiple columns:
ALTER TABLE Student
ADD CONSTRAINT CHK_Marks CHECK (Marks>=30 AND Subject='CS');
Comments
Post a Comment
your feedback is valuable.