Oracle - SPLessons

Chapter 16

Oracle Data Integrity

Oracle Data Integrity

Oracle Data integrity is an essential and basic component of information security in the database. In other words data integrity refers to the efficiency and flexibility of data stored in a database.

It is crucial that Oracle Data Integrity will control the information, And is an obedience to business regulations and guidelines decided by the database application developer or administrator .

Oracle database contains different categories of Oracle Data Integrity constraints.


Unique Constraints

By defining a column with unique constraint, then that unique constraint column will not accept duplicate values. i.e, unique constraint will accept a single character or number. It will not accept repeated values in the table.

create table <table_name>(<column_name1> datatype(size) constraint  type, <column_name2> datatype(size));

Table_name => Any accurate table in the database.

Column_name => The operation that can be perform on a column in the table.

constraint_type =>constraint type will specify the type of constraint, that going to be executed in the database.

By viewing the below example, the concept of unique constraint can be understand easily.

sql> create table stud2(stu_id int unique,stu_name varchar(255),fee int);
Query OK, 0 rows affected (0.32 sec)

sql> insert into stud2 values(1001,'rock',12000);
Query OK, 1 row affected (0.17 sec)

sql>insert into stud2 values(1002,'hairy',13000);
Query OK, 1 row affected (0.17 sec)

sql> insert into stud2 values(1003,'scena',13000);
Query OK, 1 row affected (0.16 sec)

sql> insert into stud2 values(1003,'joe',13000);
ERROR 1062 (23000): Duplicate entry '1003' for key 'stu_id'

In the above example,the unique constraint is applied to column_name emp_id in which only unique emp_id’s values will be accepted and duplicate emp_id values will not be accepted.

Not Null Constraints

By defining a column with Not Null Constraint then that Not Null Constraint column will not accept null values.
create table <table_name>( <column_name1> datatype(size)constraint_type, <column_name2> datatype(size);
Table_name => Any accurate table.

Column_name => The operation that can be perform on a column in the table.

constraint_type => constraint type will specify the type of constraint, that going to be executed in the database.

By viewing the below example, the concept of  Not Null Constraint can be easily understand.

sql> create table stud12(stu_id int not null,stu_name varchar(255)not null,fee
 int);
Query OK, 0 rows affected (0.41 sec)

sql>insert into stud12 values(1001,'jack',12000);
Query OK, 1 row affected (0.10 sec)

sql> insert into stud12 values(null,'jack',12000);
ERROR 1048 (23000): Column 'stu_id' cannot be null

In the above example if Not Null Constraint is applied on a column_name emp_id, then that column will accepts the values other than null values.

Primary Key Constraints

By defining a column with primary key constraint then that primary key constraint will not accept null and duplicate values and a table can contain only one primary key.

Primary key=Unique+Not null
Unique constraints can accept null values where as primary key will not accept null and duplicate values.

create table <table_name> (data type(size) constraint type, data type(size),constraint(column_name1);
Table_name => Any accurate table.

Column_name =>The operation that can be performed in the column of a table.

constraint_type => constraint type will specify the type of constraint,that going to be executed in the database.

By viewing the below example,the concept of primary key constraint can be easily understand.

sql> create table prod13(prod_id int not null auto_increment,prod_name varchar
(255)not null,primary key(prod_id));
Query OK, 0 rows affected (0.52 sec)

sql> insert into prod13 (prod_name) values ('mobile');
Query OK, 1 row affected (0.05 sec)

sql> insert into prod13 (prod_name) values ('laptop');
Query OK, 1 row affected (0.09 sec)

sql> insert into prod13 (prod_name) values ('system');
Query OK, 1 row affected (0.09 sec)

sql> select * from prod13;
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
|       1 | mobile    |
|       2 | laptop    |
|       3 | system    |
+---------+-----------+
3 rows in set (0.00 sec)

In the above example, the column prod_id is given not null auto increment constraint and at the same time in another column prod_id is given primary key constraint. In such case there is no need to insert each and every value, just writing the prod_name it will automatically display the prod_id values.

Foreign key constraints

When a column is defined with foreign key constraint then that foreign key constraint column will accept only the values of given primary key constraint column.

  • A table contains single primary key constraint column.
  • A table can have any number of foreign key constraints columns.
  • Table in which primary constraint key is defined is called parent key.
  • A table in which foreign key constraint column is defined is called child table.
  • A parent table can have any number of child tables, but a child table can have only one parent table.
  • Without inserting parent record in parent table, insertion of child record in child table is not possible.
  • Without deleting child record from child table, parent record cannot be deleted from parent table.

Create table <table_name1>(<column_name1> datatype(size), <column_name2>datatype(size), primary key constraint(column_name1));

create table <table_name2>(<column_name1( datatype(size), <column_name2>datatype(size), datatype(size), FOREIGN KEY constraint(column_name1) REFERENCES(column_name1); );

Table_name => Any accurate table.

Column_name => The operation that can be perform on a column in the table.

constraint_type => constraint type will specify the type of constraint,that going to be executed in the database.

By viewing the below example,the concept of foreign key constraint can be easily understand.

sql> CREATE TABLE Student14(stud_id int AUTO_INCREMENT,name varchar(30)NOT NUL
L,age int NOT NULL,PRIMARY KEY(stud_id));
Query OK, 0 rows affected (0.37 sec)
sql> insert into Student14 (name,age) values ('mike',25);
Query OK, 1 row affected (0.11 sec)

sql> insert into Student14 (name,age) values ('kate',35);
Query OK, 1 row affected (0.05 sec)

sql> select * from Student14;
+---------+------+-----+
| stud_id | name | age |
+---------+------+-----+
|       1 | mike |  25 |
|       2 | kate |  35 |
+---------+------+-----+
2 rows in set (0.00 sec)
sql> CREATE TABLE enrol15(rol_no int NOT NULL AUTO_INCREMENT,stud_id int NOT N
ULL,PRIMARY KEY(rol_no),FOREIGN KEY(stud_id)REFERENCES Student14(stud_id));
Query OK, 0 rows affected (0.34 sec)
sql> INSERT INTO enrol15 (stud_id) values (1);
Query OK, 1 row affected (0.38 sec)

sql> INSERT INTO enrol15 (stud_id) values (2);
Query OK, 1 row affected (0.18 sec)

sql> INSERT INTO enrol15 (stud_id) values (3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`employee`.`enrol15`, CONSTRAINT `enrol15_ibfk_1` FOREIGN KEY (`stud_id`)
REFERENCES `student14` (`stud_id`))
sql> select * from enrol15;
+--------+---------+
| rol_no | stud_id |
+--------+---------+
|      1 |       1 |
|      2 |       2 |
+--------+---------+
2 rows in set (0.06 sec)

In the above example, 2 tables are created i.e, studentd14 and enrol15.In student14 table primary key constraint is assigned to column stud_id,so no need to assign stud_id values just write name and age values, stud_id values will be taken automatically. In the table eroll15 foreign key is assigned to stud_id with reference to primary key.In such case in enroll15 table assigning stud_id values has to satisfy the stud_id of student14 values, if not it will terminate the values.

Check constraints

A check constraint column specifies a condition to be true or not for all the row in the database table. If the case is said to be untrue,then that statement will be diminish.

The check constraints advantages are, the capacity to accomplish explicit integrity guidelines and regulations. For instance, check constraints contains the following rules under employee bench.

  • Each employee salary should be less than 15000.
  • The commission values must be less than the salary values in the database table.

Alter table <table_name> ADD constraint constraint type CHECK column_name condition;

Alter =>This command will alter the database table.

Table_name =>The accurate table in the database.

Constraint type => constraint type will specify the type of constraint,that going to be executed in the database.

Referential integrity

This integrity is a database concept that establish a relationship among the tables that are consistent.The concept of referential integrity states that if a record does’t combined to the table that consists the foreign key except there is a reciprocal record in the linked tables. It assigns to another table only when the table contains the foreign key attribute.

For example consider two tables,employee and manager table.The employee table contain foreign key attribute entitled Managed by the records in the Managers table.Referential integrity enforces the following steps.

  • If a row in the Manager table is deleted,then corresponding rows of employee table must be eliminated.  
  • In Manager table if a column contains primary key attribute and when a row in the table changes, all corresponding records of Employee table should be altered using a cascading modernize.

Summary

  • Oracle Data integrity – Means storing data more efficiently.
  • Unique constraint will not accept duplicate values.
  • Not Null constraint will not accept Null values.
  • Constraint of primary key does’t not accept null and duplicate values.
  • Foreign key  values  will accept only the primary key values.
  • Check constraints allows only true values and roll_back if the condition is false.
  • Referential integrity – States that a table can be refer to another table using foreign key constraint.