Cascade constraints : Constraint « Table « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table
3. Constraint

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>