3 Replies Latest reply: Apr 18, 2013 6:31 AM by Karthick_Arp RSS

    PRIMARY KEY CASCADE;

    EV259
      ALTER TABLE Table_Name DROP PRIMARY KEY CASCADE;
      drop table Table_name cascade constraints;
      What exactly happening here with the above two statements. in the first step we are dropping Primary key and in the next dropping the table itself.

      Am in a confusion and not able get what is going exactly.

      Thanks
        • 1. Re: PRIMARY KEY CASCADE;
          Manik
          Check this blunt example:
          CREATE TABLE Departments (
             Code INTEGER PRIMARY KEY NOT NULL,
             Name varchar2(100) NOT NULL
           );
           
           CREATE TABLE Employees (
             empcode INTEGER PRIMARY KEY NOT NULL,
             Name varchar2(100) NOT NULL ,
             LastName varchar2(100) NOT NULL ,
             Department INTEGER NOT NULL , 
             CONSTRAINT fk_Departments_Code FOREIGN KEY(Department) 
             REFERENCES Departments(Code)
           );
          Now if I try to drop departments:
          drop table departments;
          
          ORA-02449: unique/primary keys in table referenced by foreign keys
          SO I do this:
          alter table departments drop primary key cascade;
          
          drop table departments;
          
          table dropped!
          Cheers,
          Manik.
          • 2. Re: PRIMARY KEY CASCADE;
            EV259
            Thank you for your clear explanation. I got it.
            • 3. Re: PRIMARY KEY CASCADE;
              Karthick_Arp
              In both the case the PRIMARY KEY column reference to the FOREIGN KEY column of the child table
              gets detached by using CASCADE CONSTRAINTS

              Here is an example

              Case 1 : Drop the primary key column

              This is my set up.
              SQL> create table parent (id integer primary key);
               
              Table created.
               
              SQL> create table child (id integer, constraint fk foreign key (id) references parent(id));
               
              Table created.
               
              SQL> alter table parent add name varchar2(10);
               
              Table altered.
              
              SQL> select constraint_name, constraint_type
                2    from user_constraints
                3   where table_name in ('PARENT', 'CHILD');
               
              CONSTRAINT_NAME                C
              ------------------------------ -
              FK                             R
              SYS_C00657539                  P
              Now i try to drop the column ID from PARENT
              SQL>  alter table parent drop column id;
               alter table parent drop column id
                                              *
              ERROR at line 1:
              ORA-12992: cannot drop parent key column
              I cant as a reference to PARENT.ID exist in CHILD.ID

              Now, i drop PARENT.ID and ask oracle to CASCADE it to its child tables. So oracle Drops PARENT.ID
              and removes all reference constraints to this key. So the foreign key of CHILD.ID is removed.
              SQL> alter table parent drop column id cascade constraints;
               
              Table altered.
              
              SQL> select constraint_name, constraint_type
                2    from user_constraints
                3   where table_name in ('PARENT', 'CHILD');
               
              no rows selected
              Case 2 : Drop the table with primary key

              This is my setup
              SQL> drop table parent;
               
              Table dropped.
               
              SQL> drop table child;
               
              Table dropped.
               
              SQL> create table parent (id integer primary key);
               
              Table created.
               
              SQL> create table child (id integer, constraint fk foreign key (id) references parent(id));
               
              Table created.
               
              SQL> select constraint_name, constraint_type
                2    from user_constraints
                3   where table_name in ('PARENT', 'CHILD');
               
              CONSTRAINT_NAME                C
              ------------------------------ -
              FK                             R
              SYS_C00657541                  P
              Now i drop PARENT, you can see i am unable to do that because a reference to PARENT.ID exist in CHILD.ID.
              SQL> drop table parent;
              drop table parent
                         *
              ERROR at line 1:
              ORA-02449: unique/primary keys in table referenced by foreign keys
              Now i drop the table and ask oracle to CASCADE it to the CHILD table. You can see the foreign key is removed from CHILD table
              SQL> drop table parent cascade constraints;
               
              Table dropped.
               
              SQL> select constraint_name, constraint_type
                2    from user_constraints
                3   where table_name in ('PARENT', 'CHILD');
               
              no rows selected
               
              SQL>