how to drop unnamed primary key constraint

Question

i created a table in oracle 10g as following :

CREATE TABLE studentTbl(
 studId VARCHAR2(20) PRIMARY KEY,
 StudName VARCHAR2(40)
 );

And now want to remove primary key from studId without dropping this column. I searched about it and found Dropping unnamed constraints but it also could n't help me. Couldn't get sys.columns table or view.

Thanks

Answer 1

alter table studenttbl drop primary key;

More details in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3001.htm#i2103997

^{(What is this tbl suffix for? Sounds like a terrible naming convention)}

Answer 2

The question you link to isn't for Oracle. The system view USER_CONSTRAINTS has a list of all constraints. The P in the CONSTRAINT_TYPE column indicates that it's a primary key; you can utilise this to find other constraints.

You can use this view to generate the DDL necessary to drop the constraint (or to view other information).

For example:

CREATE TABLE studentTbl(
 studId VARCHAR2(20) PRIMARY KEY,
 StudName VARCHAR2(40)
 );

Table created.


select 'alter table ' || table_name
       || ' drop constraint ' || constraint_name || ';'
  from user_constraints
 where table_name = 'STUDENTTBL'
   and constraint_type = 'P';

'ALTERTABLE'||TABLE_NAME||'DROPCONSTRAINT'||CONSTRAINT_NAME||';'
----------------------------------------------------------------------

alter table STUDENTTBL drop constraint SYS_C0017725;