Constraints constraints of oracle data base management systems

constraints of oracle data base management systems

1. Copyright © 2009, Oracle. All rights reserved.
Constraints

2. Copyright © 2009, Oracle. All rights reserved.9-2
While creating tables in a relational database, you must ensure that
no one should enter invalid data in it. Therefore, you need to apply
certain rules and constraints for columns that specify the kind of
data to be stored.
If the checks are not applied while defining and creating tables, the
data stored in the tables can become redundant.
Therefore, it is important to ensure that the data stored in tables is
complete and consistent. The concept of maintaining consistency
and completeness of data is called Data Integrity.
Implementing Data Integrity

3. Copyright © 2009, Oracle. All rights reserved.9-3
Data integrity:
Is enforced to ensure that the data in a database is accurate,
consistent, and reliable.
Is broadly classified into the following categories:
Entity integrity
Domain integrity
Referential integrity
Implementing Data Integrity (Contd.)

4. Copyright © 2009, Oracle. All rights reserved.9-4
Implementing Data Integrity (Contd.)
Entity Integrity
Ensures that each row can be uniquely identified by an attribute
called the primary key. The primary key column contains unique
value in all the rows. In addition, this column cannot be NULL.
Domain Integrity
Ensures that only a valid range of values is stored in a column. It
can be enforced by restricting the type of data, the range of values,
and the format of the data.
Referential Integrity
Ensures that the values of the foreign key match the value of the
corresponding primary key.

5. Copyright © 2009, Oracle. All rights reserved.9-5
Implementing Data Integrity (Contd.)
Constraints:
•Enforce rules that must be followed to maintain consistency
and correctness of the data.
•Constraints prevent the deletion of a table if there are
dependencies.
•The following constraint types are valid:
–NOT NULL
–UNIQUE
–PRIMARY KEY
–FOREIGN KEY
–CHECK
Types of Constraints

6. Copyright © 2009, Oracle. All rights reserved.9-6
Guidelines
•You can name a constraint, or the Oracle server generates a name with
the SYS_Cn format.
•Create a constraint at either of the following times:
–At the time of creation of table
–After the table has been created
•Check the existing data if added after the creation of the table
•Define a constraint at the column or table level.
•View a constraint in the data dictionary.
•Can be created by using either of the following statements:
CREATE TABLE statement
ALTER TABLE statement

7. Copyright © 2009, Oracle. All rights reserved.9-7
•Syntax:
CREATE TABLE [schema.]table
(columndatatype[DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
column[CONSTRAINT constraint_name] constraint_type,
Defining Constraints
•Column-level constraint:
•Table-level constraint:
Note
NOT NULL constraints must be defined at the column level.

8. Copyright © 2009, Oracle. All rights reserved.9-8
CREATE TABLE Student(
Sno Number(5),
Sname Varchar2(12),
. . .
Fees Number(8,2),
CONSTRAINT sno_pkPRIMARY KEY(Sno)
);
CREATE TABLE Student(
Sno Number(5) CONSTRAINT sno_pkPRIMARY KEY,
Sname Varchar2(12),
Course Varchar2(10),
Fees Number(8,2)
);
1
2
Defining Constraints
•Column-level constraint:
•Table-level constraint:

9. Copyright © 2009, Oracle. All rights reserved.9-9
•Ensures that null values are not permitted for the column:
NOT NULLconstraint
(No row can contain
a null value for
this column.)
Absence of NOT NULL
constraint
(Any row can contain
a null value for this
column.)
NOT NULL
constraint
…
NOT NULLConstraint

10. Copyright © 2009, Oracle. All rights reserved.9-10
NOT NULLConstraint
Columns Data Type Checks
EmployeeID Number(5) NOT NULL
LeaveStartDate Date NOT NULL
LeaveEndDate Date NOT NULL
LeaveReason varchar(100)
LeaveType Char(2) NOT NULL
•Consider the following EmployeeLeavetable.
CREATE TABLE EmployeeLeave(
EmployeeID Number(5) NOT NULL,
LeaveStartDateDate NOT NULL.
LeaveEndDate Date NOT NULL,
LeaveReason Varchar2(15),
LeaveType Char(2) NOT NULL
);

11. Copyright © 2009, Oracle. All rights reserved.9-11
UNIQUE Constraint
•Ensures that every value in a column or set of columns (key) be
unique—that is, no two rows of a table can have duplicate values in a
specified column or set of columns.
•The column (or set of columns) included in the definition of the
UNIQUEkey constraint is called the unique key.
•If the UNIQUE constraint comprises more than one column, that group
of columns is called a composite unique key.
•UNIQUE constraints enable the input of nulls unless you also define
NOT NULLconstraints for the same columns.
•The Oracle server enforces the UNIQUE constraint by implicitly
creating a unique index on the unique key column or columns.

12. Copyright © 2009, Oracle. All rights reserved.9-12
EMPLOYEES
UNIQUEconstraint
INSERT INTO
Not allowed:
already exists
Allowed
…
UNIQUE Constraint

13. Copyright © 2009, Oracle. All rights reserved.9-13
DefiningUNIQUE Constraint
CREATE TABLE EmpInf(
EmpCode Number(5),
EmpName Varchar2(15) NOT NULL,
Email Varchar2(25) CONSTRAINT email_unqUNIQUE,
Salary Number(8,2)
);
•Column-level constraint:
•Table-level constraint:
CREATE TABLE EmpInf(
EmpCode Number(5),
. . .
Salary Number(8,2),
CONSTRAINT email_unqUNIQUE(Email)
);

14. Copyright © 2009, Oracle. All rights reserved.9-14
DefiningUNIQUE Constraint
CREATE TABLE EmpInf(
EmpCode Number(5),
EmpName Varchar2(15) NOT NULL,
Email Varchar2(25)
CONSTRAINT email_nnNOT NULL
CONSTRAINT email_unqUNIQUE,
Salary Number(8,2)
);
•More than One Constraint

15. Copyright © 2009, Oracle. All rights reserved.9-15
PRIMARY KEY Constraint
•A PRIMARY KEY constraint creates a primary key for the table. Only
one primary key can be created for each table.
•The PRIMARY KEY constraint is a column or set of columns that
uniquely identifies each row in a table.
•This constraint enforces uniqueness of the column or column
combination and ensures that no column that is part of the primary key
can contain a null value.
•It is similar to the Unique key constraint except that it does not allow
NULLrow.

16. Copyright © 2009, Oracle. All rights reserved.9-16
DEPARTMENTS
PRIMARY KEY
INSERT INTO
Not allowed
(null value)
Not allowed
(50 already exists)
…
PRIMARY KEY Constraint

17. Copyright © 2009, Oracle. All rights reserved.9-17
DefiningPRIMARY KEY Constraint
CREATE TABLE Department(
DeptNo Number(5)CONSTRAINT dept_pkPRIMARY KEY,
DeptName Varchar2(15) NOT NULL,
Location Varchar2(25) NOT NULL,
);
•Column-level constraint:
•Table-level constraint:
CREATE TABLE Department(
DeptNo Number(5),
DeptName Varchar2(15) NOT NULL,
Location Varchar2(25) NOT NULL,
CONSTRAINT dept_pkPRIMARY KEY(DeptNo)
);

18. Copyright © 2009, Oracle. All rights reserved.9-18
DefiningPRIMARY KEY Constraint
•Composite Primary Key
Create Table EmployeeLeave(
EmployeeID Number(5),
LeaveStartDate Date,
LeaveEndDate Date NOT NULL,
LeaveReason Varchar2(50),
LeaveType Char(2) NOT NULL,
CONSTRAINT comp_KeyPRIMARY KEY(EmployeeID,LeaveStartDate)
);

19. Copyright © 2009, Oracle. All rights reserved.9-19
FOREIGNKEY Constraint
•The FOREIGN KEY (or referential integrity) constraint designates a
column or combination of columns as a foreign key and establishes a
relationship between a primary key or a unique key in the same table
or a different table.
•Removes the inconsistency in two tables when the data in one table
depends on the data in another table.
•Always refers the primary key column of another table or in the same
table.
Guidelines
•A foreign key value must match an existing value in the parent table or
be NULL.
•Foreign keys are based on data values and are purely logical, rather
than physical pointers.

20. Copyright © 2009, Oracle. All rights reserved.9-20
DEPARTMENTS
EMPLOYEES
FOREIGN
KEY
INSERT INTO Not allowed
(9 does not
exist)
Allowed
PRIMARY
KEY
…
…
FOREIGNKEY Constraint

21. Copyright © 2009, Oracle. All rights reserved.9-21
DefiningFOREIGN KEY Constraint
CREATE TABLE Department(
DeptNo Number(4)CONSTRAINT dept_pkPRIMARY KEY,
DeptName Varchar2(15) NOT NULL,
Location Varchar2(25) NOT NULL,
);
•Create Department Table
•Column-level Foreign Key constraint:
CREATE TABLE EmpDetails(
EmpCode Number(5) CONSTRAINT emp_pkPRIMARY KEY,
EmpName Varchar2(15) NOT NULL,
Job Varchar2(12),
DeptNo Number(4) CONSTRAINT dept_fkREFERENCES Department(DeptNo),
Salary Number(8,2)
);

22. Copyright © 2009, Oracle. All rights reserved.9-22
DefiningFOREIGN KEY Constraint
•Table-level Foreign Key constraint:
CREATE TABLE EmpDetails(
EmpCodeNumber(5),
EmpNameVarchar2(15) NOT NULL,
Job Varchar2(12),
Salary Number(8,2),
DeptNo Number(4),
CONSTRAINT emp_pkPRIMARY KEY(EmpCode),
CONSTRAINT dept_fkFOREIGN KEY(DeptNo) REFERENCES Department(DeptNo)
);

23. Copyright © 2009, Oracle. All rights reserved.9-23
DefiningFOREIGN KEY Constraint
•Self Reference
CREATE TABLE Employee(
EmpNo Number(5) CONSTRAINT emp_pkPRIMARY KEY,
EmpName Varchar2(15) NOT NULL,
Job Varchar2(12),
Salary Number(8,2),
MgrID Number(5),
DeptNo Number(4),
CONSTRAINT emp_fkFOREIGN KEY(MgrID) REFERENCES Employee,
CONSTRAINT dept_fkFOREIGN KEY(DeptNo) REFERENCES Department(DeptNo)
);

24. Copyright © 2009, Oracle. All rights reserved.9-24
•FOREIGN KEY: Defines the column in the child table at the
table-constraint level
•REFERENCES: Identifies the table and column in the parent
table
•ONDELETECASCADE: Deletes the dependent rows in the
child table when a row in the parent table is deleted
•ONDELETESETNULL: Converts dependent foreign key
values to null
FOREIGN KEY Constraint Keywords

25. Copyright © 2009, Oracle. All rights reserved.9-25
DefiningFOREIGN KEY Constraint with Keyword
CREATE TABLE BatchDetails(
BatchCode Varchar2(6) CONSTRAINT batch_pkPRIMARY KEY,
CrsName Varchar2(15) NOT NULL,
Duration Number(3),
Faculty Varchar2(5),
CONSTRAINT batch_fkFOREIGN KEY(Faculty) REFERENCES Faculty(F_Code)
ON DELETE SET NULL
);
•BatchDetails Table
•Faculty Table
CREATE TABLE Faculty(
F_Code Varchar2(5) CONSTRAINT fac_pkPRIMARY KEY,
F_Name Varchar2(15) NOT NULL,
Exp Number(3),
);

26. Copyright © 2009, Oracle. All rights reserved.9-26
CREATE TABLE EmpDetails(
EmployeeIDNumber(5) CONSTRAINT emp_pkPRIMARY KEY,
EmpName Varchar2(15) NOT NULL,
Salary Number(8,2)
);
•EmpInf Table
•EmployeeLeave Table
Create Table EmployeeLeave(
EmployeeID Number(5),
LeaveStartDateDate,
LeaveEndDateDate NOT NULL,
LeaveReasonVarchar2(50),
LeaveType Char(2) NOT NULL,
CONSTRAINT comp_KeyPRIMARY KEY(EmployeeID,LeaveStartDate),
CONSTRAINT empid_fkFOREIGN KEY(EmployeeID) REFERENCES EmpDetails(EmployeeID)
ON DELETE CASCADE
);
DefiningFOREIGN KEY Constraint with Keyword

27. Copyright © 2009, Oracle. All rights reserved.9-27
•Defines a condition that each row must satisfy
•The following expressions are not allowed:
–References to CURRVAL, NEXTVAL, LEVEL, and ROWNUMpseudo
columns
–Calls to SYSDATE, UID, USER, and USERENVfunctions
–Queries that refer to other values in other rows
..., salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0),...
CHECK Constraint
•A single column can have multiple CHECK constraints that refer to the
column in its definition.
•There is no limit to the number of CHECK constraints that you can define
on a column.

28. Copyright © 2009, Oracle. All rights reserved.9-28
DefiningCHECK Constraint
•Column-level Check constraint:
Create Table EMPL(
EmpCode Number(5),
EmpName Varchar2(12),
Job Varchar2(15),
Bsal Number(6) CONSTRAINT sal_chkCHECK(Bsal>=6000)
);
•Table-level Check constraint:
Create Table Project(
ProjCode Number(5),
EmployeeID Number(5),
Description Varchar2(50),
StartDate Date,
EndDate Date,
CONSTRAINT chk_dateCHECK(StartDate<=EndDate)
);

29. Copyright © 2009, Oracle. All rights reserved.9-29
DefiningCHECK Constraint Using IN Operator
Create Table EmployeeLeave(
EmployeeID Number(5),
LeaveStartDateDate,
LeaveEndDateDate NOT NULL,
LeaveReason Varchar2(50),
LeaveTypechar(2) CONSTRAINT leave_chkCHECK( LeaveTypeIN('CL','SL','PL') ),
CONSTRAINT comp_keyPRIMARY KEY(EmployeeID,LeaveStartDate),
CONSTRAINT empid_fkFOREIGN KEY(EmployeeID) REFERENCES EmpInf(EmployeeID)
);

30. Copyright © 2009, Oracle. All rights reserved.9-30
DefiningCHECK Constraint Using BETWEEN Operator
Create Table EMPL(
EmpCode Number(5),
EmpName Varchar2(12),
Job Varchar2(15),
Bsal Number(6) CONSTRAINT sal_chkCHECK(Bsal>=6000 and Bsal<=15000)
(OR)
Bsal Number(6) CONSTRAINT sal_chkCHECK(BsalBETWEEN 6000 AND 15000)
);

31. Copyright © 2009, Oracle. All rights reserved.9-31
Constraints Example
Create Table EmployeeLeave(
EmployeeID Number(5),
LeaveStartDateDate DEFAULT SYSDATE,
LeaveEndDateDate,
LeaveReasonVarchar2(50),
LeaveType Char(2) DEFAULT 'CL',
CONSTRAINT comp_keyPRIMARY KEY(EmployeeID,LeaveStartDate),
CONSTRAINT empid_fkFOREIGN KEY(EmployeeID) REFERENCES EmpInf(EmployeeID),
CONSTRAINT leave_chkCHECK(LeaveTypeIN('CL','SL','PL')),
CONSTRAINT date_chkCHECK(LeaveStartDate<=LeaveEndDate)
);

32. Copyright © 2009, Oracle. All rights reserved.9-32
Managing Constraints
UsetheALTERTABLEStatementto:
•AddorDropaconstraint,butnotModifyits structure
•EnableorDisableconstraints
•AddaNOT NULLconstraintbyusingtheMODIFY clause
Adding a Constraint:
ALTERTABLEtablename
ADD [ CONSTRAINT constraint_name] constraint_type( column_name) ;

33. Copyright © 2009, Oracle. All rights reserved.9-33
Managing Constraints (Contd.)
•Adding PRIMARY KEY Constraint
•EmpDetails Table
Create Table EmpDetails(
EmpCode Number(5),
EmpName Varchar2(12),
Job Varchar2(15),
Bsal Number(6)
DeptNo Number(5)
);
ALTER TABLE EmplDetailsADD CONSTRAINT empno_pkPRIMARY KEY(Empno);
•Adding FOREIGN KEY Constraint
ALTER TABLE EmplDetailsADD CONSTRAINT deptno_fk
FOREIGN KEY(DeptNo) REFERENCES Department(DeptNo)

34. Copyright © 2009, Oracle. All rights reserved.9-34
Managing Constraints (Contd.)
•Adding CHECK Constraint
•EmpDetails Table
Create Table EmpDetails(
EmpCode Number(5),
EmpName Varchar2(12),
Job Varchar2(15),
Bsal Number(6)
DeptNo Number(5)
);
ALTER TABLE EmpDetailsADD CONSTRAINT bsal_chkCHECK(Bsal>=8000)
•Adding NOT NULL Constraint by using MODIFY Clause
ALTER TABLE EmpDetailsMODIFY EmpNameNOT NULL

35. Copyright © 2009, Oracle. All rights reserved.9-35
Dropping a Constraint
ALTERTABLEtablename
DROP PRIMARY KEY | UNIQUE ( column_name)
CONSTRAINT constraint_name[CASCADE];
Managing Constraints (Contd.)
•Dropping bsal_chk Constraint
To drop a constraint, you can identify the constraint name from the
USER_CONSTRAINTS and USER_CONS_COLUMNS datadictionary views.
SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS;
ALTER TABLE EmpDetailsDROP CONSTRAINT bsal_chk;

36. Copyright © 2009, Oracle. All rights reserved.9-36
Managing Constraints (Contd.)
•Dropping Dependent Constraints
ALTER TABLE EmpDetailsDROP CONSTRAINT deptno_fk;
ALTER TABLE Department DROP PRIMARY KEY ;
(OR)
ALTER TABLE Department DROP CONSTRAINT deptno_pk;
•Using CASCADE Option
TheCASCADE option of the DROP clause causes any dependent
Constraints also to be dropped.
ALTER TABLE Department DROP PRIMARY KEY CASCADE ;
(OR)
ALTER TABLE Department DROP CONSTRAINT deptno_pkCASCADE ;

37. Copyright © 2009, Oracle. All rights reserved.9-37
Managing Constraints (Contd.)
Disabling Constraints
•Execute the DISABLE clause of the ALTER TABLE statement to deactivate
an integrity constraint.
•ApplytheCASCADEoptiontodisabledependent integrityconstraints.
ALTERTABLEtablename
DISABLE CONSTRAINT constraint_name[ CASCADE ];
•Disabling a Constraint
ALTER TABLE EmpDetailsDISABLE CONSTRAINT bsal_chk;
•Using CASCADE option
ALTER TABLE Department DISABLE CONSTRAINT deptno_pkCASCADE ;

38. Copyright © 2009, Oracle. All rights reserved.9-38
Managing Constraints (Contd.)
Enabling Constraints
•Activate an integrity constraint currently disabled in the table.
•A UNIQUE or PRIMARY KEY index is automatically created when you enable
ALTERTABLEtablename
ENABLE CONSTRAINT constraint_name;
•Enabling a Constraint
ALTER TABLE EmpDetailsENABLE CONSTRAINT bsal_chk;
Guidelines
•If you enable a constraint, that constraint applies to all the data in the table. All the
data in the table must fit the constraint.
•Enabling a primarykey constraint that was disabled with the CASCADE option does
not enable any foreignkeysthataredependentupontheprimarykey.

39. Copyright © 2009, Oracle. All rights reserved.9-39
Cascading Constraints
•The CASCADE CONSTRAINTS clause is usedalong with the
DROP TABLE and DROP COLUMN clause.
•TheCASCADE CONSTRAINTS clausedropsall referential
integrity constraints that refer to the primary and unique keys
defined on the dropped columns.
•The CASCADE CONSTRAINTS clause also drops all multicolumn
constraints defined on the dropped columns.

40. Copyright © 2009, Oracle. All rights reserved.9-40
Cascading Constraints
Create Table Project(
ProjCode Number(5) CONSTRAINT proj_pkPRIMARY KEY,
Description Varchar2(50),
StartDate Date,
EndDate Date,
CONSTRAINT chk_dateCHECK(StartDate<=EndDate)
);
•Consider Project Table
ALTER TABLE Project DROP (ProjCode) ; (Assume ProjCodehas Dependencies)
ALTER TABLE Project DROP (StartDate) ;
•An error is returned for the following statements:
•Reason:
oProjCode Column has Dependencies
oStartDate Column is referenced by multicolum constraint chk_date
oProject Table is a Parent Table
DROP TABLE Project;

41. Copyright © 2009, Oracle. All rights reserved.9-41
Cascading Constraints
ALTER TABLE Project DROP COLUMN ProjCodeCASCADE CONSTRAINTS ;
Dropping Constrained Column
Dropping Parent Table
DROP TABLE Project CASCADE CONSTRAINTS ;
ALTER TABLE Project DROP COLUMN StartDateCASCADE CONSTRAINTS ;
ALTER TABLE Project DROP (ProjCode,StartDate) CASCADE CONSTRAINTS ;
•If all columns referenced by the constraints defined on the dropped columns, then
CASCADE CONSTRAINTS is not required.
•For example consider Employee Table, assuming that no other referential constraints
from other tables refer to column EmpNo,
ALTER TABLE Employee DROP (EmpNo, MgrID) ;

42. Copyright © 2009, Oracle. All rights reserved.9-42
Viewing Constraints
•After creating a table, you can confirm its existence by issuing a DESCRIBE
command. The only constraint that you can verify is the NOTNULL constraint.
To viewall constraints on your table, query the USER_CONSTRAINTS table.
SELECT Constraint_Name, Constraint_Type, Search_Condition, Table_Name
FROMUser_Constraints;
Note:
Constraints that are not named by the table owner receive the system-
assignedconstraintname.
In constraint type, Cstands for CHECK,P for PRIMARYKEY, R for referential
integrity, and U for UNIQUE key.
NoticethattheNOT NULLconstraintisreallyaCHECKconstraint.

43. Copyright © 2009, Oracle. All rights reserved.9-43
Viewing Constraints (Contd.)
SELECT Constraint_Name, Column_Name
FROMUser_Cons_Columns
WHERE Table_Name= ‘EMPLOYEE’ ;
•Viewing the columns associated with the constraint names.

44. Copyright © 2009, Oracle. All rights reserved.9-44
UPDATE employees
SET department_id = 55
WHERE department_id = 110;
Violating Constraints

45. Copyright © 2009, Oracle. All rights reserved.9-45
Violating Constraints
You cannot delete a row that contains a primary key that is
used as a foreign key in another table.
DELETE FROM departments
WHERE department_id = 60;