Oracle PL/SQL/Table/Table Reference

Reference a table

 
SQL>
SQL>
SQL> create table gender_tab (
  2        gender_id  char(1),
  3        gender_nm  varchar2(6),
  4        constraint gender_pk primary key ( gender_id ),
  5        constraint gender_id_ck check ( gender_id in ( "M", "F" ) )
  6  );
Table created.
SQL>
SQL> insert into gender_tab values ( "F", "Female" );
1 row created.
SQL> insert into gender_tab values ( "M", "Male" );
1 row created.
SQL>
SQL>
SQL> create table people (first_name        varchar2(20),
  2                       last_name         varchar2(25),
  3                       gender         char(1)
  4  );
Table created.
SQL>
SQL> alter table people
  2  add constraint people_gender_fk
  3  foreign key ( gender )
  4  references gender_tab;
Table altered.
SQL>
SQL> insert into people values ( "Celia", "Rice", "M" );
1 row created.
SQL> insert into people values ( "Robert", "Black", "M" );
1 row created.
SQL> insert into people values ( "Linda", "Green", "F" );
1 row created.
SQL>
SQL> drop table gender_tab;
drop table gender_tab
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL>
SQL> drop table gender_tab cascade constraints;
Table dropped.
SQL>
SQL>
SQL>
SQL>

Using Inheritance Relationships

    
SQL> CREATE TABLE Person
  2      (id         VARCHAR2(10) NOT NULL,
  3       name       VARCHAR2(20),
  4       address    VARCHAR2(35),
  5       PRIMARY KEY (id));
Table created.
SQL>
SQL> CREATE TABLE emp
  2      (id         VARCHAR2(10) NOT NULL,
  3       course     VARCHAR2(10),
  4       year       VARCHAR2(4),
  5       PRIMARY KEY (id),
  6       FOREIGN KEY (id) REFERENCES Person ON DELETE CASCADE);
Table created.
SQL>
SQL>
SQL> CREATE TABLE Staff
  2      (id         VARCHAR2(10)  NOT NULL,
  3       department VARCHAR2(10),
  4       room_no    VARCHAR2(4),
  5       PRIMARY KEY (id),
  6       FOREIGN KEY (id) REFERENCES Person ON DELETE CASCADE);
SQL>
SQL>
SQL>
SQL> drop table Person;
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL> drop table Staff;
SQL>
SQL>