How can I delete a primary or foreign key?

I am using PostgreSQL 7.4.1 (only through psql)
I know, that the command

ALTER TABLE OFFICES
DROP PRIMARY KEY (CITY);

and its foreign key equivalent:

ALTER TABLE SALESREPS
DROP CONSTRAINT
FOREIGN KEY (REP_OFFICE)
REFERENCES OFFICES;

don't work in PostgreSQL because they are not implemented. However, isn't
there another way of removing them?
I also tried to drop the index associated with the primary key, but it is not
permitted.

Anyone with any idea?
--
Tibor

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: How can I delete a primary or foreign key?

On Fri, 20 Feb 2004, Tibor wrote:
[color=blue]
> I am using PostgreSQL 7.4.1 (only through psql)
> I know, that the command
>
> ALTER TABLE OFFICES
> DROP PRIMARY KEY (CITY);
>
> and its foreign key equivalent:
>
> ALTER TABLE SALESREPS
> DROP CONSTRAINT
> FOREIGN KEY (REP_OFFICE)
> REFERENCES OFFICES;
>
> don't work in PostgreSQL because they are not implemented. However, isn't
> there another way of removing them?[/color]

That's not the correct syntax for ALTER TABLE ... DROP CONSTRAINT.

ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: How can I delete a primary or foreign key?

On Fri, 20 Feb 2004, Tibor wrote:
[color=blue]
> I am using PostgreSQL 7.4.1 (only through psql)
> I know, that the command
>
> ALTER TABLE OFFICES
> DROP PRIMARY KEY (CITY);
>
> and its foreign key equivalent:
>
> ALTER TABLE SALESREPS
> DROP CONSTRAINT
> FOREIGN KEY (REP_OFFICE)
> REFERENCES OFFICES;
>
> don't work in PostgreSQL because they are not implemented. However, isn't
> there another way of removing them?
> I also tried to drop the index associated with the primary key, but it is not
> permitted.
>
> Anyone with any idea?[/color]

It's an alter table:

alter table offices drop constraint constraint_name

where constraint name is usually tablename_pkey

assuming it was created the normal way, on a 7.4 box.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: How can I delete a primary or foreign key?

I forgot to mention that I have tried numerous variations.
The one quoted in the original mail was from "The Complete Reference" series.
I've also tried the one that the \h command suggests:

ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;

but all I got was:

ERROR: syntax error at or near "foreign" at character 37

the DROP CONSTRAINT clause doesn't recognise either PRIMARY or FOREIGN KEY
option. (not implemented, I guess)

On Friday 20 Feb 2004 16:42, you wrote:[color=blue]
> On Fri, 20 Feb 2004, Tibor wrote:[color=green]
> > I am using PostgreSQL 7.4.1 (only through psql)
> > I know, that the command
> >
> > ALTER TABLE OFFICES
> > DROP PRIMARY KEY (CITY);
> >
> > and its foreign key equivalent:
> >
> > ALTER TABLE SALESREPS
> > DROP CONSTRAINT
> > FOREIGN KEY (REP_OFFICE)
> > REFERENCES OFFICES;
> >
> > don't work in PostgreSQL because they are not implemented. However, isn't
> > there another way of removing them?[/color]
>
> That's not the correct syntax for ALTER TABLE ... DROP CONSTRAINT.
>
> ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT | CASCADE][/color]

--
Tibor

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

re: How can I delete a primary or foreign key?

tibor wrote:[color=blue]
> I forgot to mention that I have tried numerous variations.
> The one quoted in the original mail was from "The Complete Reference" series.
> I've also tried the one that the \h command suggests:
>
> ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;[/color]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

You are forgetting the name of the constraint.

Sincerely,

Joshua D. Drake



[color=blue]
>
> but all I got was:
>
> ERROR: syntax error at or near "foreign" at character 37
>
> the DROP CONSTRAINT clause doesn't recognise either PRIMARY or FOREIGN KEY
> option. (not implemented, I guess)
>
> On Friday 20 Feb 2004 16:42, you wrote:
>[color=green]
>>On Fri, 20 Feb 2004, Tibor wrote:
>>[color=darkred]
>>>I am using PostgreSQL 7.4.1 (only through psql)
>>>I know, that the command
>>>
>>>ALTER TABLE OFFICES
>>> DROP PRIMARY KEY (CITY);
>>>
>>>and its foreign key equivalent:
>>>
>>>ALTER TABLE SALESREPS
>>> DROP CONSTRAINT
>>>FOREIGN KEY (REP_OFFICE)
>>> REFERENCES OFFICES;
>>>
>>>don't work in PostgreSQL because they are not implemented. However, isn't
>>>there another way of removing them?[/color]
>>
>>That's not the correct syntax for ALTER TABLE ... DROP CONSTRAINT.
>>
>>ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT | CASCADE][/color]
>
>[/color]


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: How can I delete a primary or foreign key?

On Fri, 20 Feb 2004, tibor wrote:
[color=blue]
> I forgot to mention that I have tried numerous variations.
> The one quoted in the original mail was from "The Complete Reference" series.
> I've also tried the one that the \h command suggests:
>
> ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;[/color]

\h shows me
ALTER TABLE [ ONLY ] name [ * ]
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

constraint_name isn't something like: FOREIGN KEY ...
it's the name given to the constraint (preferably at add time with the
CONSTRAINT constraint_name clause otherwise it's given an arbitrary name).

If you use \d tablename
You should see something like:
Foreign-key constraints:
"$1" FOREIGN KEY (b) REFERENCES a(a)

And the drop would look like
ALTER TABLE tablename DROP CONSTRAINT "$1";


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

re: How can I delete a primary or foreign key?

you are right. the correct version is:

ALTER TABLE name_of_table
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

On Friday 20 Feb 2004 17:53, you wrote:[color=blue]
> tibor wrote:[color=green]
> > I forgot to mention that I have tried numerous variations.
> > The one quoted in the original mail was from "The Complete Reference"
> > series. I've also tried the one that the \h command suggests:
> >
> > ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;[/color]
>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> You are forgetting the name of the constraint.
>
> Sincerely,
>
> Joshua D. Drake
>[color=green]
> > but all I got was:
> >
> > ERROR: syntax error at or near "foreign" at character 37
> >
> > the DROP CONSTRAINT clause doesn't recognise either PRIMARY or FOREIGN
> > KEY option. (not implemented, I guess)
> >
> > On Friday 20 Feb 2004 16:42, you wrote:[color=darkred]
> >>On Fri, 20 Feb 2004, Tibor wrote:
> >>>I am using PostgreSQL 7.4.1 (only through psql)
> >>>I know, that the command
> >>>
> >>>ALTER TABLE OFFICES
> >>> DROP PRIMARY KEY (CITY);
> >>>
> >>>and its foreign key equivalent:
> >>>
> >>>ALTER TABLE SALESREPS
> >>> DROP CONSTRAINT
> >>>FOREIGN KEY (REP_OFFICE)
> >>> REFERENCES OFFICES;
> >>>
> >>>don't work in PostgreSQL because they are not implemented. However,
> >>> isn't there another way of removing them?
> >>
> >>That's not the correct syntax for ALTER TABLE ... DROP CONSTRAINT.
> >>
> >>ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT |
> >> CASCADE][/color][/color][/color]

--
Tibor Harcsa

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

re: How can I delete a primary or foreign key?

On Friday 20 February 2004 16:04, tibor wrote:[color=blue]
> I forgot to mention that I have tried numerous variations.
> The one quoted in the original mail was from "The Complete Reference"[/color]

Which book is this?

Look in the SQL Command reference - ALTER TALBLE
[color=blue]
> series. I've also tried the one that the \h command suggests:
>
> ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;[/color]

The \h says the same as the manuals:
ALTER TABLE [ ONLY ] table [ * ]
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

If you have a table called "mytab" and a foreign-key constraint called
"myfkey" then you would use

ALTER TABLE mytab DROP CONSTRAINT myfkey;

If your constaint has a generated name like $1 then you'll want to quote it
"$1"
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

re: How can I delete a primary or foreign key?

On Friday 20 Feb 2004 18:00, you wrote:[color=blue]
> On Fri, 20 Feb 2004, tibor wrote:[color=green]
> > I forgot to mention that I have tried numerous variations.
> > The one quoted in the original mail was from "The Complete Reference"
> > series. I've also tried the one that the \h command suggests:
> >
> > ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;[/color]
>
> \h shows me
> ALTER TABLE [ ONLY ] name [ * ]
> DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
>
> constraint_name isn't something like: FOREIGN KEY ...
> it's the name given to the constraint (preferably at add time with the
> CONSTRAINT constraint_name clause otherwise it's given an arbitrary name).
>[/color]

You are perfectly right. I simply forgot to put in the name of the constraint.

[color=blue]
> If you use \d tablename
> You should see something like:
> Foreign-key constraints:
> "$1" FOREIGN KEY (b) REFERENCES a(a)
>
> And the drop would look like
> ALTER TABLE tablename DROP CONSTRAINT "$1";[/color]

Thank you for your help and the info!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

re: How can I delete a primary or foreign key?

On Friday 20 Feb 2004 18:08, you wrote:[color=blue]
> On Friday 20 February 2004 16:04, tibor wrote:[color=green]
> > I forgot to mention that I have tried numerous variations.
> > The one quoted in the original mail was from "The Complete Reference"[/color]
>
> Which book is this?[/color]


SQL: The Complete Reference, McGraw-Hill/Osborne, 2nd Edition, 2002
(James G. Groff and Paul N. Weinberg)

[color=blue]
>
> Look in the SQL Command reference - ALTER TABLE
>[color=green]
> > series. I've also tried the one that the \h command suggests:
> >
> > ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;[/color]
>
> The \h says the same as the manuals:
> ALTER TABLE [ ONLY ] table [ * ]
> DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
>
> If you have a table called "mytab" and a foreign-key constraint called
> "myfkey" then you would use
>
> ALTER TABLE mytab DROP CONSTRAINT myfkey;
>
> If your constaint has a generated name like $1 then you'll want to quote it
> "$1"[/color]

Thanks.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: How can I delete a primary or foreign key?

I've just received this and found it useful.

On Friday 20 Feb 2004 20:04, you wrote:[color=blue]
> Le Vendredi 20 Février 2004 16:26, Tibor a écrit :[color=green]
> > Anyone with any idea?[/color]
>
> I would suggest using pgAdmin III from http://www.pgadmin.org, which writes
> the required SQL for you. It is a very convenient way to learn PostgreSQL
> internals.
>
> Cheers, Jean-Michel[/color]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: How can I delete a primary or foreign key?

Ok. the winning combination for deleting a primary key is:

ALTER TABLE PARENT_KEY DROP CONSTRAINT PARENT_TYPE_PKEY CASCADE;

without cascade, you get the message:

NOTICE: constraint $1 on table parents depends on index parent_type_pkey
ERROR: cannot drop constraint parent_type_pkey on table parent_key because
other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Thanks for the help!

The other bonus that I've meanwhile found the delection of foreign keys too:

Let's suppose that I've got a table "parents" which has a foreign key.
with the \d parents command I get :

Table "public.parents"
Column | Type | Modifiers
--------+-----------------------+-----------
child | character varying(10) | not null
type | character varying(10) |
pname | character varying(10) |
Foreign-key constraints:
"$1" FOREIGN KEY ("type") REFERENCES parent_key(par_type)

Now, the name of the foreign key is $1 and this is what I have to delete:

ALTER TABLE PARENTS DROP CONSTRAINT "$1"; /* the double quote is important */

On Friday 20 Feb 2004 16:56, you wrote:[color=blue]
> On Fri, 20 Feb 2004, Tibor wrote:[color=green]
> > I am using PostgreSQL 7.4.1 (only through psql)
> > I know, that the command
> >
> > ALTER TABLE OFFICES
> > DROP PRIMARY KEY (CITY);
> >
> > and its foreign key equivalent:
> >
> > ALTER TABLE SALESREPS
> > DROP CONSTRAINT
> > FOREIGN KEY (REP_OFFICE)
> > REFERENCES OFFICES;
> >
> > don't work in PostgreSQL because they are not implemented. However, isn't
> > there another way of removing them?
> > I also tried to drop the index associated with the primary key, but it is
> > not permitted.
> >
> > Anyone with any idea?[/color]
>
> It's an alter table:
>
> alter table offices drop constraint constraint_name
>
> where constraint name is usually tablename_pkey
>
> assuming it was created the normal way, on a 7.4 box.[/color]

--
Tibor Harcsa
tiborh@opendiary.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

re: How can I delete a primary or foreign key?

I got it to work by using the form:
ALTER TABLE tablename DROP CONSTRAINT constraint name;
No reference to FOREIGN KEY, just use the constraint name.
On Friday 20 February 2004 08:04 am, tibor wrote:[color=blue]
> I forgot to mention that I have tried numerous variations.
> The one quoted in the original mail was from "The Complete Reference"
> series. I've also tried the one that the \h command suggests:
>
> ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;
>
> but all I got was:
>
> ERROR: syntax error at or near "foreign" at character 37
>
> the DROP CONSTRAINT clause doesn't recognise either PRIMARY or FOREIGN KEY
> option. (not implemented, I guess)
>
> On Friday 20 Feb 2004 16:42, you wrote:[color=green]
> > On Fri, 20 Feb 2004, Tibor wrote:[color=darkred]
> > > I am using PostgreSQL 7.4.1 (only through psql)
> > > I know, that the command
> > >
> > > ALTER TABLE OFFICES
> > > DROP PRIMARY KEY (CITY);
> > >
> > > and its foreign key equivalent:
> > >
> > > ALTER TABLE SALESREPS
> > > DROP CONSTRAINT
> > > FOREIGN KEY (REP_OFFICE)
> > > REFERENCES OFFICES;
> > >
> > > don't work in PostgreSQL because they are not implemented. However,
> > > isn't there another way of removing them?[/color]
> >
> > That's not the correct syntax for ALTER TABLE ... DROP CONSTRAINT.
> >
> > ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT |
> > CASCADE][/color][/color]

--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

re: How can I delete a primary or foreign key?

I got it to work by using the form:
ALTER TABLE tablename DROP CONSTRAINT constraint name;
No reference to FOREIGN KEY, just use the constraint name.
On Friday 20 February 2004 08:04 am, tibor wrote:[color=blue]
> I forgot to mention that I have tried numerous variations.
> The one quoted in the original mail was from "The Complete Reference"
> series. I've also tried the one that the \h command suggests:
>
> ALTER TABLE PARENTS DROP CONSTRAINT FOREIGN KEY (TYPE) CASCADE;
>
> but all I got was:
>
> ERROR: syntax error at or near "foreign" at character 37
>
> the DROP CONSTRAINT clause doesn't recognise either PRIMARY or FOREIGN KEY
> option. (not implemented, I guess)
>
> On Friday 20 Feb 2004 16:42, you wrote:[color=green]
> > On Fri, 20 Feb 2004, Tibor wrote:[color=darkred]
> > > I am using PostgreSQL 7.4.1 (only through psql)
> > > I know, that the command
> > >
> > > ALTER TABLE OFFICES
> > > DROP PRIMARY KEY (CITY);
> > >
> > > and its foreign key equivalent:
> > >
> > > ALTER TABLE SALESREPS
> > > DROP CONSTRAINT
> > > FOREIGN KEY (REP_OFFICE)
> > > REFERENCES OFFICES;
> > >
> > > don't work in PostgreSQL because they are not implemented. However,
> > > isn't there another way of removing them?[/color]
> >
> > That's not the correct syntax for ALTER TABLE ... DROP CONSTRAINT.
> >
> > ALTER TABLE tablename DROP CONSTRAINT constraint_name [RESTRICT |
> > CASCADE][/color][/color]

--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org