is there any difference DROP PRIMARY KEY in oracle and postgres?

In PostgreSQL, there is no “DROP PRIMARY KEY”. Instead, you need to use
DROP INDEX index_name;

http://www.postgresql.org/docs/9.4/interactive/sql-dropindex.html

On Thu, Nov 5, 2015 at 10:52 PM, M Tarkeshwar Rao <
m.tarkeshwar.rao@ericsson.com> wrote:

Hi,

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?

Regards

Tarkeshwar

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 11/05/2015 07:52 PM, M Tarkeshwar Rao wrote:

Hi,

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?

No. As a PRIMARY KEY is technically a constraint, we use ALTER TABLE
DROP CONSTRAINT.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
New rule for social situations: "If you think to yourself not even
JD would say this..." Stop and shut your mouth. It's going to be bad.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 is too long (>63 characters)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/5/2015 11:25 PM, Thomas Kellerer wrote:

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

is that syntax in the sql standard? or is it just an oraclism? do any
other major sql's have an equivalent ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John R Pierce <pierce@hogranch.com> writes:

On 11/5/2015 11:25 PM, Thomas Kellerer wrote:

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

is that syntax in the sql standard? or is it just an oraclism?

AFAICS there is no such syntax in SQL:2011 --- it only offers
"ALTER TABLE name DROP CONSTRAINT name".

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general