Re: How to drop Foreign key?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 13 Nov 1999 15:12:08 -0500
Message-ID: <2cMtOBX1oL6+E1IN+wBs=30VhCSu@4ax.com>

A copy of this was sent to "Jingyan" <jingyan.xu_at_tst2000.com> (if that email address didn't require changing) On Sat, 13 Nov 1999 11:05:23 -0800, you wrote:

>Hi,
>I use following SQL to drop primary key of T1:
>ALTER TABLE T1
>DROP PRIMARY KEY;
>
>But, I use following SQL to drop Foreign key of T1 and I got a error:
>ALTER TABLE T1
>DROP FOREIGN KEY REFERENCES T2;
>

You have to drop the named constraint. you can find the references constraints this way:

tkyte_at_ORACLE8I.WORLD> select constraint_name from user_constraints where table_name = 'C'
  2 and constraint_type = 'R';

CONSTRAINT_NAME

---

SYS_C00933 and then you drop it like this:

tkyte_at_ORACLE8I.WORLD> alter table c drop constraint SYS_C00933;

Table altered.

It works best if YOU name all constraints, eg:

tkyte_at_ORACLE8I.WORLD> create table c ( y int,

  2                                   constraint c_fk_2_p foreign key(y)

references p(x) );

Table created.

tkyte_at_ORACLE8I.WORLD> 
tkyte_at_ORACLE8I.WORLD> 
tkyte_at_ORACLE8I.WORLD> alter table c drop constraint c_fk_2_p;

Table altered.

that way -- no searching for the system generated name.

>Would you like to tell me how to drop it.
>Thanks in advance
>Jingyan Xu
>
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Nov 13 1999 - 14:12:08 CST