How to use Foreign Key and other Constraint in Oracle 10g XE

Foreign key is a non-key attribute which depends on a Primary key column of another table.

Here in below given example in table EMPL, DEPTID is foreign key column and there is another table named DEPTL which is Primary key table and DEPTID

is Primary Key Column.

If Foreign Key is present in same Primary Key table then it called Self Referential Integrity.

Primary Key and Unique Key both uniquely define a tuple(Row).

but the difference is that Primary key can't take null values and Unique Key can take Multiple null values.

Step 1: Create a Primary Key table DEPTL with DEPTID as Primary Key Column.

 CREATE TABLE DEPTL(DEPTID NUMBER(4) PRIMARY KEY,DEPTNAME VARCHAR2(20))  

Step 2: Create a table EMPL

 CREATE TABLE EMPL(ID NUMBER(20) NOT NULL,EMAIL VARCHAR2(20) CONSTRAINT EMPL_EMAIL_UK UNIQUE,  
 SALARY NUMBER(8,2) CHECK(sALARY>1000),DEPTID NUMBER(4),  
 CONSTRAINT EMPL_ID_PK PRIMARY KEY(ID),  
 CONSTRAINT EMPL_DEPT_FK FOREIGN KEY(DEPTID)  
 REFRENCES DEPTL(DEPTID))  

NOTE: NOT NULL Constraint can't be apply as a Table Level Constraint.