• Sharebar

Usage Create table statement with foreign key constraint will create table with referential integrity.
Foreign Key holds the reference to another table column value.It is also known as references constraint.
A foreign key means that values in one table must also appear in another table.A references constraint is only applied at SQL ‘insert’ and ‘delete’ times.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Let us create an EMPLOYEE table which holds the employee information like name ,id and salary.
Here emp_id is the primary key column.

SQL> create table EMPLOYEE(name VARCHAR2(50),emp_id NUMBER,salary NUMBER(8,2), CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (emp_id));

Table created

Now create an Employee address table which holds the address details of the employees.The address table is linked to EMPLOYEE table through the foreign key emp_id;

SQL> create table EMPLOYEE_ADDRESS(address varchar2(200),emp_id number,CONSTRAINT EMP_ADD_FK FOREIGN KEY (emp_id) REFERENCES EMPLOYEE(emp_id));

Table created

Now let us insert one record in EMPLOYEE table.

SQL> insert into EMPLOYEE values('ABCD',1,12345);

1 row inserted

Now let us add address for this particular employee in EMPLOYEE_ADDRESS table.

SQL> insert into EMPLOYEE_ADDRESS values('abcd efgh',1);

1 row inserted

Now while entering the data in EMPLOYEE_ADDRESS table the data should be already present in EMPLOYEE table.Otherwise foreign key violation will result.

SQL> insert into EMPLOYEE_ADDRESS values('abcd efgh',8);
ORA-02291: integrity constraint (TEST.EMP_ADD_FK) violated - parent key not found

ORA-02291: integrity constraint (TEST.EMP_ADD_FK) violated – parent key not found

Also if we delete a row from the EMPLOYEE table and if a reference exists in EMPLOYEE_ADDRESS table oracle will throw an error showing reference integrity dependency.

SQL> delete from EMPLOYEE where emp_id =1;
ORA-02292: integrity constraint (TEST.EMP_ADD_FK) violated - child record found

ORA-02292: integrity constraint (TEST.EMP_ADD_FK) violated – child record found
Also Read,CREATE TABLE statement: create a table with composite primary key

Oracle PL/SQL:CREATE TABLE statement: create a table with primary key.
Oracle PL/SQL:Create Table
Oracle/PLSQL: Foreign Keys with ON DELETE CASCADE option
ORA-02449: unique/primary keys in table referenced by foreign keys
ORA-02292: integrity constraint violated – child record found
ORA-02291: integrity constraint violated – parent key not Found
Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key
Technorati Tags:
, ,

 
Be Sociable, Share!
SEO Powered by Platinum SEO from Techblissonline