Re: dropping a primary key

Nagaraj Rekha (nr1_at_Ra.MsState.Edu) wrote:

: Hi netters!!
 

: I have been trying to drop aprimary key and a foriegn key from a table with
: out success. I have been using the Disable command.
: I would appreciate it if any one could tell me where I am going wrong and
: thr right procedure to do it.

: Rekha Nagaraj
: E-mail: rekha_at_erc.msstate.edu

--
Here's my guess to drop a primary key:

1) Get the primary key constraint name for the table

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'the table with the primary key';

2) Get the referencing tables and their foreign key constraint names for 
this primary key constraint

SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME = 'constraint name above';

3) Alter each of the referencing table dropping the foreign key 
constraint to the primary key table

ALTER TABLE for each table name returned above DROP CONSTRAINT 
corresponding foreign key constraint name above;

4) Alter the primary key table dropping the primary key constraint

ALTER TABLE table name with the primary key DROP CONSTRAINT primary key 
constraint name;

Here's my guess to drop the foreign key:

1) Find the constraint name that is the foreign key constraint

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'table in question'
AND CONSTRAINT_TYPE = 'R';

2) Alter the table and drop the foreign key constraint

ALTER TABLE_NAME table in question DROP CONSTRAINT constraint_name 
returned above;

Murray Kaiser                  | Usual gutless disclaimer.. 
Nova Scotia Power              | Opinions are mine and not necessarily
(Murray.Kaiser_at_NSPower.NS.CA)  | shared by my employer