Oracle SQL - Add foreign key to a table


Add foreign key to a table

create table  emp
( empno      NUMBER(4)    constraint E_PK        primary key
                          constraint E_EMPNO_CHK check (empno > 7000)
, ename      VARCHAR2(8)  constraint E_NAME_NN   not null
, init       VARCHAR2(5)  constraint E_INIT_NN   not null
, job        VARCHAR2(8)
, mgr        NUMBER(4)    constraint E_MGR_FK    references emp
, bdate      DATE         constraint E_BDAT_NN   not null
, msal       NUMBER(6,2)  constraint E_MSAL_NN   not null
, comm       NUMBER(6,2)
, deptno     NUMBER(2)    default 10
,                         constraint E_SALES_CHK check
                                                  + nvl2(comm,          1,0) = 1)
) ;
create table departments
( deptno NUMBER(2)     constraint D_PK          primary key
                       constraint D_DEPTNO_CHK  check (mod(deptno,10) = 0)
, dname  VARCHAR2(10)  constraint D_DNAME_NN    not null
                       constraint D_DNAME_UN    unique
                       constraint D_DNAME_CHK   check (dname = upper(dname))
, location VARCHAR2(8) constraint D_LOC_NN      not null
                       constraint D_LOC_CHK     check (location = upper(location))
, mgr    NUMBER(4)     constraint D_MGR_FK      references emp
) ;

--Adding a Foreign Key Constraint

alter table emp add
(constraint E_DEPT_FK foreign key (deptno) references departments);

Related Topics