Re: How to drop a foreign key

From: Valery Yourinsky <vsu_at_bill.mts.ru>
Date: Wed, 11 Apr 2001 11:05:41 +0400
Message-ID: <3AD40245.7006185F@bill.mts.ru>

David YEUNG wrote:
>
> I have a table A which contains a foreign key reference to
> table B's primary key, e.g.:
>
> create table A (
> ...
> fid varchar2(16) not null,
> ...
> foreign key (fid) references B(pid) on delete cascade
> )
>
> where pid is the primary key of the table B.
>
> Can someone tell me how to drop (or disable) the foreign key in table A.

   ALTER TABLE a DISABLE CONSTRAINT constraint_name

   OR

   ALTER TABLE a DROP CONSTRAINT constraint_name

   If constraint name is unknown you can select it    from user_constraints or all_constraints or dba_constraints    dictionary view.

SELECT T.table_name      AS Table_name
     , T.constraint_name AS constraint_name
     , T.constraint_type AS constraint_type
     --
     , R.table_name      AS Ref_Table
     , R.constraint_name AS ref_constraint
     , R.constraint_type AS ref_constraint_type

FROM user_constraints T

   , user_constraints R

WHERE T.constraint_type   = 'R'
  AND T.r_constraint_name = R.constraint_name
  AND T.r_owner           = R.owner

Valery Yourinsky

-- 
Oracle8 Certified DBA
Moscow, Russia

Received on Wed Apr 11 2001 - 02:05:41 CDT