Integrity Constraints in SQL
In this article let us understand the classification and usage of Integrity Constraints in SQL.
Integrity Constraints are a kind of conditions or rules which we can specify on data in a table.
*Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER table statement.
Example:
*In a table we have salary column we need to insert all the records in the table, at that time we can apply a condition that salary should be greater than certain value.
*Similarly, if we have cities column we can give a condition that only certain cities should be inserted in the table.
SQL constraints:
NOT NULL:
This constraint ensures that a column cannot have a null value.
However you can apply NOT NULL on any number of columns.
Example:
We are trying to create table and insert values into the table student.
Using NOT NULL:
USE mydb;
CREATE TABLE STUDENT
(sno int(3), NOT NULL, sname varchar(10), marks int(30));
Note: While creating the table we have given not null as a value.
INSERT into student values(NULL,’KIRAN’,70); //ERROR says column sno cannot be null.
*Internally we have given a column as NOTNULL while creating the table hence we cannot pass null value while inserting the values.
*we cannot specify one or more columns as not null.
UNIQUE:
This constraint ensures that all the values in a column are different and this can be applies on both column and table level.
Column level :
CREATE TABLE STUDENT (sno int(3) Unique, sname varchar(10),marks int(3));//sno will not allow duplicate values
Table level:
CREATE TABLE STUDENT (sno int(3), sname varchar(10), marks int(3), Unique(sno));
Note:
*Table level doesn't support any constraints hence we will perform the constraints on column level.
*If we specify the constraint immediately after the column definition that is column level.
*If we specify the constraint after completion of all the columns at the end that is called table level.
Example:
INSERT into STUDENT values(101.’swetha’,10);
INSERT into STUDENT values (101,’abcd’,20); // this gives an error as sno cannot be duplicated.
Note: It allows NULL values.
INSERT into STUDENT values(null,’suresh’,20);
INSERT into STUDENT values(null,’mahesh’,30); // this doesnot give any error when we pass null value multiple times as null is not a number or value.
Primary Key:
This is a combination of a NOTNULL and UNIQUE constraints and this key uniquely identifies each row in a table
*This will not allow duplicate values and also null values.
*This can create constraint both in column level and table level.
Note: for one table we can have only one primary key
Command:
USE mydb;
drop table student;
create table student
(sno int(3) not null,unique,
sname varchar(10),
marks int(3));
Note: Instead of this we can say primary key in the place of unique+not null as shown below.
Command:
Create table student
(sno int(3) Primary key,
sname varchar(10),
marks int(3)); // this is for column level
Command:
Create table student
(sno int(3).
sname varchar(10),
marks int(3),
Primary key(sno)); // this is for Tablelevel
Note: We can create primary key on combination of two columns called as composite primary key.
*Composite key can be applied only at table level.
Insert into student values(101,’swetha’,50); //valid
Insert into student values(101,’suresh’,60); //invalid..sno should be unique
Insert into student values(null,’suresh’,50); //invalid ..NULL is not allowed.
Consider a table
SNAME GRADE //are the columns
A a
B b
X a
A b //are the values in columns
Note: Every record is a unique record like the combination of Aa,Bb,Xa,Ab is not repeated anywhere in the table so when a primary key is applied on 2 columns then it is called composite key.
Example:
Create table student
(sno int(3),
class int(3),
sname varchar(10),
marks int(3),
Primary key(sno,class)); // Combination of columns. This is valid.
FOREIGN KEY:
*This is used to link two tables.
*This is a field or collection of fields in one table, that refers to the primary key in the another table.
*The table containing the foreign key is called the child table and the table containing the candidate key is called the referenced or parent table.
Consider,
There are 2 tables
*Table 1 is a child table. That is the employee table.
This has the columns empid, empname, salary, department number.
*Table 2 is a parent table.That is the department table.
This has the columns department number, department name.
*The data entered in department number in employee table should be a part of department number in department table.
*Also, if we want to remove a record from department table from department number column it will not allow us as employee table records are dependent on department table records.
*Further, if we want to remove any record from department table we need to first remove all the dependent records from employee table.
*If we remove the records from parent table, corresponding child records should be removed but bi default, which is not possible.
In order to combine these 2 tables we need to have some relationship between the tables.So we need to locate common column in the tables.Sometimes if the column name is different that is not an issue but datatype should be same.
Note: Sno from library table should be referring to sno from school table.
//Parent table
CREATE TABLE SCHOOL(
sno int(3),
sname varchar(15),
marks int(3),
primary key(sno));
INSERT into school values(101,’swetha’,90);
INSERT into school values(102,’John’,70);
INSERT into school values(103,’King’,80);
Select * from school;
//Child table
CREATE TABLE LIBRARY
(sno int(3), FOREIGN KEY (sno) REFERENCES school(sno),
book_name varchar(10));
INSERT into library values(102,’java’);
INSERT into library values(108,’c’);//not allowed
INSERT into library values(null,’dot net’);// this is allowed.
Note:
*When we are trying to insert sno as 102 in library table it will check if the same sno 102 is present in student table and it should match then it will allow us to enter the data in child table(library table).
*When we try to insert sno as 108 in library table it will give an error as the value is not there in parent table.
*Also,Null as a value is accepted.
Now,
1)When we try to insert a record into child table, that column will be referred in parent table.
2) If we try remove parent table record directly without deleting records from child table this will not be possible.
3) To make this possible, first, we need to delete dependent record from child table and then we need to delete the record from parent table.
On delete cascade :
How this works?
If we try to delete a record from parent table directly, it will be removed and the corresponding records will also be deleted from the child table.
Note:
When we remove the parent record child records will be removed automatically.
*Usually we cannot delete rows from parent table unless we delete corresponding row from child table.
*We can delete rows from the parent table and corresponding child table row as well(at the same time) by using ONDELETECASCASCADE option.
//Parent table
CREATE TABLE SCHOOL(
sno int(3),
sname varchar(15),
marks int(3),
primary key(sno));
INSERT into school values(101,’swetha’,90);
INSERT into school values(102,’John’,70);
INSERT into school values(103,’King’,80);
Select * from school;
delete from school where sno=101;
//Child table
CREATE TABLE LIBRARY
(sno int(3), FOREIGN KEY (sno) REFERENCES school(sno) ON DELETE CASCADE,
book_name varchar(10));
INSERT into library values(102,’java’);
INSERT into library values(101,’c’);
Select * from library;
Check:
This constraint ensures that all values in a column satisfies a specific condition.
Command:
Create table student(
sno int(5),
sname varchar(15),
marks int(5) check(marks between 50 and 100));
INSERT into student values(101,’swetha’,90);//valid
INSERT into student values(102,’John’,40); //in valid
INSERT into student values(103,’King’,105);//invalid as marks should be between 50 and 100 as per condition.
select * from student;
Example 2:
Create table loc
(city varchar(15) check (city in(‘HYDERABAD’,’CHENNAI’,’DELHI’)),
country varchar(15),
pin int(8));
insert into values(‘HYDERABAD’,’INDIA’,12345); //valid
insert into values(‘MUMBAI’,’INDIA’,644566);// invalid
insert into values(‘DELHI’,’INDIA’,678445); //valid
select * from loc;
Default:
This constraint sets a default value for a column when no value is specifies.
The default value will be added to all new records If no other value is specified.
Command:
CREATE TABLE Orders
(ID int(5),
orderNumber int(5),
OrderDate datetime DEFAULT now());
//NOW() is a in built function which returns current date and time in MySQL.
//Everytime we do not have to enter date and time as DEFAULT constraint is given.
Insert into Orders values(101,1342342,’2019–05–21 15:25:10');// We have passed these values manually but we want these values to be displayed bi default.
Insert into Orders(ID,Ordernumber) values(101,2456);
Insert into Orders(ID,OrderNumber) values(102,2457);
select * from Orders;// when we execute this the output should have updated values that is time and date.
That is all about constraints in SQL. In the next and final part, we will be learning about AutoIncrement, Limit class and view in SQL
