Implement the following integrity constraints on the following database EMP (Empno, Ename, Job, Salary, Mgr, Comm, Hiredate, Deptno) DEPT(Deptno, Dname, Location) a. Primary Key b. Foreign Key c. Unique d. Not NULL e. Check

EXERCISE : 2

AIM : Implement the following integrity constraints on the following database EMP (Empno, Ename, Job, Salary, Mgr, Comm, Hiredate, Deptno) DEPT(Deptno, Dname, Location)                                                                                                   a. Primary Key b. Foreign Key c. Unique d. Not NULL e. Check

Description :

Constraints

v  KEY CONSTRAINTS

·         Super key : set of one or more attributes that uniquely identifies a tuple in a relation is called as a super key.

·         Candidate key : minimal set of attributes that uniquely identifies a tuple in a relation is called as a candidate key.

·         Primary key : it is a key which uniquely identifies a  tuple in a relation . the two properties of primary key are unique and not null.

·         Alternate key: an alternate key is a key that can be work as a primary key .basically it is a candidate key that is not a primary key.

·         Foreign key: ensure that referential integrity of the data in one table to match values in another table . ensure that the foregin key in the child table match with the primary key in the parent table.

 

v  INTEGRITY CONSTRAINTS

·         Unique key : unique key is a set of one or more fields/columns of  a table that uniquely identify  a record in database table .it is like primary key but it can accept only one null value and it cannot have duplicate values.

·         Check : ensures that the value in a field meets  a specified condition.

·         Not NULL : indicates that a field cannot store a NULL value.

 

v  Constraints according to the aim :

a) Primary Key  constraint :

The primary key constraint uniquely identifies each record in atable. They must contain UNIQUE values and cannot contain NULL values. A table can have only ONE primary key and in the table, this primary key can consist of single or multiple columns/fields .

Syntax :

Create table table_name(attribute name domaintype  primary key , .. );

                                              Or

By using alter :

Alter table table_name add constraint constraint_name primary key(attribute);

Example :

create table dept(deptno int primary key,dname varchar2(20),location varchar2(20));

Desc dept;

Select * from dept;

b) Foreign Key Constraint:

The foreign key constraint is used to prevent actions that would destroy links between tables. A foreign key is a field or a collection of fields in one table , that refers to the primary key in another table. The table with the foreign key is called the child table , and the table with the primary key is called the referenced or parent table.

Syntax :

Create table table_name(column domain type ,… , column n domain type n , foregin key(column) , references column in parent table);

Example :

create table emp1(empno int primary key,ename varchar2(20),salary number(10),mgr real,comm real,hiredate date,deptno int , foreign key(deptno) references dept);

Desc emp;

Select * from emp;

 

 

It has two attributes :
1. ON DELETE CASCADE :
               when a primary key is deleted in the parent table then corresponding data in the child table also gets deleted.

Syntax :

Create table table_name(attribute domain type , foregin key(attribute) references parent table ON DELETE CASCADE):

Example :

create table emp1(empno int primary key,ename varchar2(20),salary number(10),mgr real,comm real,hiredate date,deptno int , foreign key(deptno) references dept ON DELETE CASCADE);

2. ON DELETE SET NULL :

When a primary key and its corresponding tuples gets deleted in parent table and then corresponding records in the child table will have the foreign key field set to null but not get deleted.

Syntax :

Create table table_name(attribute domain type , foregin key(attribute) references parent table ON DELETE SET NULL):

Example :

create table emp1(empno int primary key,ename varchar2(20),salary number(10),mgr real,comm real,hiredate date,deptno int , foreign key(deptno) references dept ON DELETE SET NULL);

3. Unique Key Constraint :

 The unique constraint imposes that every value in a column or set of columns by unique. It means that no two rows of a table can have duplicate values in a specified column or set of columns.

Syntax:

While creating table :

 Syntax  :

      Create table table_name(coloumn domain type unique);

Example :

create table dept(deptno int primary key ,dname varchar2(20),location varchar2(20) unique);

Unique constraint violation :

 

4. NOT NULL:  Indicates that a column cannot store NULL value.

Syntax :

Alter table table_name modify attribute domain type NOT NULL;

Example :

Alter table emp modify attribute varchar2(20) NOT NULL;

 

5. Check : Ensures that the value in a column meets a specific condition.

Syntax :

Alter table table_name add constraint  constraint_name check(condition);

Example :

Alter table emp add constraint s3 check(salary > 500 and salary < = 20000);

Select * from emp;