Re: How to drop foreign keys

This PL/SQL snippet, when executed from SQL*Plus, will prompt you for a table name, and then drop all of its foreign key constraints. The script assumes that you own the table, but I didn't want to write the generic (DBA) version without knowing whether you had DBA permissions on your instance. Hope it helps.

To hit tables owned by other users, you would use the view DBA CONSTRAINTS in the cursor definition and add a predicate in the cursor's WHERE clause to restrict the query to only the user you are interested in (ie., WHERE OWNER = '&USER' or something like that).

  DECLARE
   V TABLE NAME CONSTANT VARCHAR2(30) := '&TABLE NAME';        CURSOR FK CUR IS
   SELECT CONSTRAINT NAME
   FROM USER CONSTRAINTS
   WHERE TABLE NAME = V TABLE NAME
   AND CONSTRAINT TYPE = 'R';       BEGIN
   FOR CONS IN FK CUR LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || V TABLE NAME      || ' DROP CONSTRAINT ' || CONS.CONSTRAINT NAME;     DBMS OUTPUT.PUT LINE(CONS.CONSTRAINT NAME || ' dropped.');    END LOOP;
  END;
  /

-- 
Cheers,
Chris

                                   

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, MCT, OCP, CIW
                                   

"Ivan" <ivangho_at_hotmail.com> wrote in message 
news:4ada4d2b.0210070348.10811cb1_at_posting.google.com...


> Hi,

> 

> Could anybody advise me on how to drop foreign keys from a table?


> Thanks!

 --