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>