To establish a "parent-child" or a "master-detail" relationship between two tables having a common column, we make use of referential integrity constraints. To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
A value that appears in one relation for a given set of attributes also appears for a certain group of attributes in another relation. This condition is called referential integrity.
It is a rule that maintains consistency among the rows of two relations. The rule states that if there is a foreign key in one relation, either each foreign key value must match a primary key value in another relation.
Create table Department
( Denptno number(2),
Dname varchar2(20),
HOD varchar2(10),
Constraint pk_deptno Primary Key(Deptno));
Create table Employee
( EmpNo number(3),
Ename varchar2(20),
Salary number(5),
Address varchar2(20),
Deptno number(2),
Constraint pk_empno Primay Key(EmpNo)
Constraint fk_deptno Foreign Key (Deptno) References Department(Deptno));
There may be a tuple tr in r(say Department Table) that does not join with any tuple in s (say Employee Table). Such tuples are called dangling tuples. Depending on the entity set or relationship set being modeled, dangling tuples may or may not be acceptable. Here as shown in the example above record of deptid 30 is exist whose corresponding employee record does not exist so that tuple is called dangling tuples. Dangling tuples in a relation are permitted where the primary key existed but dangling tupes into the foreign key columns contains does not permit.
Database modifications can cause violations of referential integrity. While performing database modification; referential-integrity constraint rules should not be violated.
Insert:-
If a tuple t2 is inserted into r2, the system must ensure that there must be a tuple t1 in r1.
This means that if you enter an employee record into the Employee table then his/her department must have existed in the Department table under which he/she is working.
i.e. we can enter employee records who are working under departments 10,20 or 30 but not 40 cause this is not present in the Department table.
Delete:-
If a tuple t1 is deleted from r1, the system must compute the set of tuples in r2 that reference t1.
If this set is not empty, either the delete command is rejected as an error, or the tuples that reference t1 must themselves be deleted. The latter solution may lead to cascading deletions, since tuples may reference tuples that reference t1, and so on.
In short; you can't delete any department from the Department table till you delete all the employees from the Employee table working under that department. But here we can use on delete cascade macro for performing this task.
Update:-
We must consider two cases for the update: updates to the referencing relation (r2), and updates to the referenced relation (r1).
- If a tuple t2 is updated in relation r2, and the update modifies values for the foreign key, then a test similar to the insert case is made.
- If a tuple t1 is updated in r1, and the update modifies values for the primary key, then a test similar to the delete case is made.
SQL Syntax:
Create a table Employee (foreign key (Deptno) references Department(Deptno) on delete cascade on update cascade);
Foreign Key can be specified as part of the SQL create table statement by using the foreign key clause. (ie. SQL DDL statements). By default, a foreign key references the primary key attributes of the referenced table. SQL also supports a version of the references clause where a list of attributes of the referenced relation can be specified explicitly. Ie. We can simply write
Foreign key (Deptno) references Department);
When a referential integrity constraint is violated, the normal procedure is to reject the action that caused the violation, However, a foreign key clause can specify that if a delete or update action on the referenced relation violates the constraint, then, instead of rejecting the action, the system must take steps to change the tuple in the referencing relation to restoring the constraint.
Because of the clause on delete cascade associated with the foreign-key declaration, if a delete of a tuple in any Department table results in this referential-integrity constraint being violated, the system does not reject the delete. Instead, the delete “cascades to the Employee relation, deleting the tuple that refers to the Department that was deleted. Similarly, the system does not reject an update to a field referenced by the constraint if it violates it.
Referential Integrity (example from BOOK)
(referential-integrity constraints or sub-set dependencies)
A value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. This condition is called referential integrity.
create table customer
( customer_name char(20),
customer_street char(30),
customer_city char(30),
primary key (customer_name));
create table branch
( branch_name char(15),
branch_city char(30),
assets numeric(16,2),
primary key (branch_name),
check (assets>=0));
create table account
( account_number char(10),
branch_name char(15),
balance numeric(12,2),
primary key (account_number),
foreign key (branch_name) references the branch,
check (balance >=0));
create table depositor
( customer_name char(20),
account_number char(20),
primary key (customer_name, account_number),
foreign key (customer_name) references the customer,
foreign key (account_name) references account);
Foreign keys can be specified as part of the SQL create table statement by using the foreign key clause. We illustrate foreign-key declarations by using the SQL DDL definition of part of our database (as shown above in SQL statements).
The definition of the account table has a declaration of a foreign key (branch_name) referencing the branch. This foreign-key declaration specifies that for each account tuple, the branch name specified in the tuple must exist in the branch relation.
By default, in SQL a foreign key references the primary key attributes of the referenced table. SQL also supports a version of the references clause where a list of attributes of the referenced relation can be specified explicitly.
Branch_name char(15) references the branch
When a referential integrity constraint is violated, the normal procedure is to reject the action that caused the violation. However, a foreign key  clause can specify that if a delete or update action on the referenced relation violates the constraint, then, instead of rejecting the action, the system must take steps to change the tuple in the referencing relation to restore the constraint. Consider this definition of an integrity constraint on the relation account
Assertions
An assertion is a predicate expressing a condition that we wish the database to always satisfy. Domain constraints and referential integrity constraints are special forms of assertions. We have paid substantial attention to these forms of assertion because they are easily tested and apply to a wide range of database applications. However, there are many constraints that we cannot express by using only these special forms. Two examples of such constraints are:
- The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch.
- Every loan has at least one customer who maintains an account with a minimum balance of Rs. 1000.
An assertion in SQL takes the form
create assertion
When an assertion is created, the system tests it for validity. If the assertion is valid, then any future modification to the database is allowed only if it does not cause that assertion to be violated. This testing may introduce a significant amount of overhead if complex assertions have been made.
