Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
M Tarkeshwar Rao
Date:

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

 

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

From
Melvin Davidson
Date:
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.

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

From
"Joshua D. Drake"
Date:
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.

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

From
Thomas Kellerer
Date:
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.

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

From
John R Pierce
Date:
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

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

From
Tom Lane
Date:
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