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