OraFAQ Forum: SQL & PL/SQL » Remove primary key

Members

Help

Home

Home » SQL & PL/SQL » SQL & PL/SQL » Remove primary key (Oracle 10.2.0.4 on Solaris 10 )

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Remove primary key [message #429253]

Mon, 02 November 2009 16:11

Lucky A
Messages: 45
Registered: October 2007

Member

I want to remove the primary keys from specific tables on certain named columns. Also I want to drop the specific named columns from the table.
I will appreciate suggestions as to the best possible approach.

Thanks,

Lucky

Report message to a moderator

Re: Remove primary key [message #429255 is a reply to message #429253]

Mon, 02 November 2009 16:51

BlackSwan
Messages: 3367
Registered: January 2009

Senior Member

ALTER SPECIFIC TABLE DROP CONSTRAINT PK_CERTAIN_NAMED_COLUMNS

Report message to a moderator

Re: Remove primary key [message #429260 is a reply to message #429253]

Mon, 02 November 2009 19:56

Lucky A
Messages: 45
Registered: October 2007

Member

I am sorry for not being clear. How can I locate all of the Foreign keys and the columns they are referencing?

Report message to a moderator

Re: Remove primary key [message #429261 is a reply to message #429260]

Mon, 02 November 2009 20:20

BlackSwan
Messages: 3367
Registered: January 2009

Senior Member

SQL> desc all_constraints
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE				    VARCHAR2(1)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 SEARCH_CONDITION				    LONG
 R_OWNER					    VARCHAR2(30)
 R_CONSTRAINT_NAME				    VARCHAR2(30)
 DELETE_RULE					    VARCHAR2(9)
 STATUS 					    VARCHAR2(8)
 DEFERRABLE					    VARCHAR2(14)
 DEFERRED					    VARCHAR2(9)
 VALIDATED					    VARCHAR2(13)
 GENERATED					    VARCHAR2(14)
 BAD						    VARCHAR2(3)
 RELY						    VARCHAR2(4)
 LAST_CHANGE					    DATE
 INDEX_OWNER					    VARCHAR2(30)
 INDEX_NAME					    VARCHAR2(30)
 INVALID					    VARCHAR2(7)
 VIEW_RELATED					    VARCHAR2(14)

SQL> desc all_cons_columns
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 COLUMN_NAME					    VARCHAR2(4000)
 POSITION					    NUMBER

Report message to a moderator