w3resource logo


SQL REFERENTIAL INTEGRITY

SQL joining through referential integrity

rating Average rating 7 out of 10. Total 41 users rated.

<<PreviousNext>>

Description

A REFERENTIAL INTEGRITY is a database concept that is used to build and maintain logical relationships between tables to avoid logical corruption of data. It is a very useful and important part in RDBMS.

Usually referential integrity is made up by the combination of a primary key and a foreign key.

The main concept of REFERENTIAL INTEGRITY is that, it does not allow to add any record in a table that contains the foreign key unless the reference table containing a corresponding primary key.

If any record in referenced table (i.e. the table who contain primary key) is deleted, all the corresponding records in the referencing table will be deleted for the referential integrity.

Example

Sample table : agents

Sample table : orders

To get 'agent_code' and 'agent_name' from 'agents' table and 'ord_num' and 'advance_amount' form 'orders' after a joining with following conditions -

1. 'agent_code' is primary key in 'agents' table

2. 'agent_code' is foreign key in orders table which is referring to the primary key of 'agents' table.

3. 'agent_code' of 'agents' and 'orders' must be same.

the following sql statement can be used :

SELECT agents.agent_code,agents.agent_name,
orders.ord_num,orders.advance_amount
FROM agents,orders
WHERE agents.agent_code=orders.agent_code;

Output

Sql joining through referential integrity

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.



<<PreviousNext>>