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:
CREATE TABLE, create a table with primary key, oracle foreign key
Pingback: Oracle Tables: Create Table as Select | SQL and PLSQL
Pingback: Oracle Tables: Foreign Keys | Oracle Referential Integrity | SQL and PLSQL