Reference a table : Table Reference « Table « Oracle PL / SQL





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>
           
         
  










Related examples in the same category

1.Using Inheritance Relationships