Re: How to drop foreign keys

Here's an addendum to my previous post. I forgot to mention that the DBA version of the script should also include the owner name in the ALTER TABLE statement. Just to clear that up, here's the DBA version of the script:

  DECLARE
   V TABLE NAME CONSTANT VARCHAR2(30) := '&TABLE NAME';    V OWNER CONSTANT VARCHAR2(30) := '&OWNER OF TABLE';        CURSOR FK CUR IS
   SELECT CONSTRAINT NAME
   FROM DBA CONSTRAINTS
   WHERE OWNER = V OWNER
   AND TABLE NAME = V TABLE NAME
   AND CONSTRAINT TYPE = 'R';       BEGIN
   FOR CONS IN FK CUR LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || V OWNER || '.' || 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!

 --