SQL Create Table Foreign Key

This is used to reference the parent table primary key column which allows duplicates.

Foreign key always attached to the child table.

We can add this constraint in table and alter levels only.

Example:

TABLE LEVEL FOREIGN KEY    

 SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   PRIMARY KEY (empno),

   FOREIGN KEY (deptno) REFERENCES dept (deptno)

);

SQL>CREATE TABLE emp

(

   empno    NUMBER (2),

   ename    VARCHAR (10),

   deptno   NUMBER (2),

   CONSTRAINT pk PRIMARY KEY (empno),

   CONSTRAINT fk FOREIGN KEY (deptno) REFERENCES dept (deptno)

);

ALTER LEVEL FOREIGN KEY

   

SQL> ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno);

SQL> ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno);

Once the primary key and foreign key relationship has been created then you can not remove any parent record if the dependent childs exists.