Re: is there any difference DROP PRIMARY KEY in oracle and postgres? - Mailing list pgsql-general

M Tarkeshwar Rao schrieb am 06.11.2015 um 04:52:
> one thing in oracle is there any difference between “DROP PRIMARY
> KEY” used directly in oracle to drop primary key, or “DROP CONSTRAINT
> CDRAUDITPOINT_pk”, as first syntax is not available in postgres and
> we need to give primary key name as constraint to delete a key. SO
> right now to delete primary key I am using second approach, so is
> there any difference between two?


Unlike Oracle, Postgres gives the PK constraint a sensible (and reproducible) name.

So even if you did not specify a constraint name when creating the index, you know the name: it's always
"tablename_pkey".

The statement:

   create table foo (id integer primary key);

will create a PK constraint named "foo_pkey", and therefore you can drop it using:

   alter table foo drop constraint foo_pkey;

I don't know which name gets chosen when the table name is so long that adding _pkey it would yield an identifier that
istoo long (>63 characters) 

But having an "alter table drop primary key" would indeed be nice.