SQL>
SQL> create table employee (
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 employee
2 values ( 'F', 'Female' );
1 row created.
SQL>
SQL> insert into employee
2 values ( 'M', 'Male' );
1 row created.
SQL>
SQL> create table people (
2 first_name varchar2(20),
3 last_name varchar2(25),
4 gender char(1)
5 )
6 /
create table people (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
SQL> alter table people
2 add constraint people_gender_fk
3 foreign key ( gender )
4 references employee
5 /
Table altered.
SQL>
SQL> insert into people
2 values ( 'S', 'D', 'M' );
1 row created.
SQL>
SQL> insert into people
2 values ( 'C', 'Beck', 'M' );
1 row created.
SQL>
SQL> insert into people
2 values ( 'N', 'E', 'F' );
1 row created.
SQL>
SQL> drop table employee;
drop table employee
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL>
SQL> drop table employee cascade constraints;
Table dropped.
SQL>