What is Referential Rntegrity Constraint in SQL

Hi Arun,

SQL Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items".

Foreign Key at column level:

CREATE TABLE product
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY,
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

Foreign Key at table level:

CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10)
);



Thanks

Referential integrity constraint is a property in which a primary key/candidate key column is referred by someother columns in other tables.

For eg.
I have a table Employee

Emp Id(primary key) EmpName
1                                Amit
2                                Arun
3                                 Nikhil


Now i have another table Emp_Salary
EmpId(Foreign key )         Salary         Department
1                                4000                HR
2                                6000                Manager..

t
Now if i try to insert a row with EmpId=4 in Emp_Salary table, it wont allow me to insert , because there are no records for Emp Id=4 in parent table(Employee). So it restricts me to enter other values.


Also if i try to delete Emo id=2 in employee table, it will throw errror, because it is reffered by Emp_salary. so inorder to delete that u have to delete rows in child table(emp_salary) then delete parent table.

see the below links for more details.
http://en.wikipedia.org/wiki/Referential_integrity
http://msdn.microsoft.com/en-us/library/ee382827.aspx