• A value appearing in a one table for a given set of attributes also appears for another set of attributes in another table. This is called referential integrity.

• A referential integrity constraint is specified between two tables and is used to maintain the consistency among tuples in the two relations.

• The tuple in one table refers only to an existing table in another relation.

Emp Table

Department Table

• In the above example “Emp” table has “Did” as foreign key reference this is called as Referential integrity.

• Here we are forcing database to check the “Did” value key from the “Department” table while inserting any value of “Emp” table in Did column if there is no value existing in department table of that “Did” then we can not insert that value in “Emp” table.

• This helps to maintain data consistency.

Referential integrity in SQL

• Foreign key is used to show relation into two tables in relational algebra.

• This helps in maintaining consistency in database, as foreign key cannot be inserted, deleted or updated.

Syntax:

           FOREIGN KEY

           REFERENCES [schema_name.] referenced_table_name [(ref_column)]

           [ON DELETE {NO ACTION

           |CASCADE

           |SET NULL

           |SET UPDATE}]

           [ON UPDATE {NO ACTION 

           |CASCADE

           |SET NULL

           |SET DEFAULT}]

Example:

             Create table Emp(Emp_id integer,

             Emp_namevarchar(100) not null,

             Did as integer,

             Primary key(Emp_id),

             Foreign key (Did) references department

             On delete cascade

             On update cascade

            )

            Create table Department(Did integer,

            Dept_name_varchar (100) not null,

            Primary key (did)

            )