What is Referential Rntegrity Constraint in SQL

Question

Hi What is referential integrity constraint in SQL ?

Satyapriya Nayak · Answer

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

Santhosh Kumar Jayaraman · Answer

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