By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,697 Members | 1,286 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > postgresql database > questions > how can i delete a primary or foreign key?
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,697 IT Pros & Developers. It's quick & easy.

How can I delete a primary or foreign key?

P: n/a
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

Nov 22 '05 #1
Share this Question
Share on Google+
13 Replies
P: n/a

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]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #2
P: n/a
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?
I also tried to drop the index associated with the primary key, but it is not
permitted.

Anyone with any idea?


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

Nov 22 '05 #3
P: n/a
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:
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]


--
Tibor

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

Nov 22 '05 #4
P: n/a
tibor wrote:
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; ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

You are forgetting the name of the constraint.

Sincerely,

Joshua D. Drake


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:
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]


--
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

Nov 22 '05 #5
P: n/a
On Fri, 20 Feb 2004, tibor wrote:
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;


\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 ma*******@postgresql.org)

Nov 22 '05 #6
P: n/a
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:
tibor wrote:
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;


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

You are forgetting the name of the constraint.

Sincerely,

Joshua D. Drake
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:
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]


--
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

Nov 22 '05 #7
P: n/a
On Friday 20 February 2004 16:04, tibor wrote:
I forgot to mention that I have tried numerous variations.
The one quoted in the original mail was from "The Complete Reference"
Which book is this?

Look in the SQL Command reference - ALTER TALBLE
series. I've also tried the one that the \h command suggests:

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


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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #8
P: n/a
On Friday 20 Feb 2004 18:00, you wrote:
On Fri, 20 Feb 2004, tibor wrote:
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;
\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).


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

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";


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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #9
P: n/a
On Friday 20 Feb 2004 18:08, you wrote:
On Friday 20 February 2004 16:04, tibor wrote:
I forgot to mention that I have tried numerous variations.
The one quoted in the original mail was from "The Complete Reference"
Which book is this?

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


Look in the SQL Command reference - ALTER TABLE
series. I've also tried the one that the \h command suggests:

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


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"


Thanks.

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

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

Nov 22 '05 #10
P: n/a
I've just received this and found it useful.

On Friday 20 Feb 2004 20:04, you wrote:
Le Vendredi 20 Février 2004 16:26, Tibor a écrit :
Anyone with any idea?


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


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

Nov 22 '05 #11
P: n/a
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:
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?
I also tried to drop the index associated with the primary key, but it is
not permitted.

Anyone with any idea?


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.


--
Tibor Harcsa
ti****@opendiary.com

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

Nov 22 '05 #12
P: n/a
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:
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:
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]


--
Adrian Klaver
ak*****@comcast.net

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

http://archives.postgresql.org

Nov 23 '05 #13
P: n/a
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:
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:
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]


--
Adrian Klaver
ak*****@comcast.net

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

http://archives.postgresql.org

Nov 23 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Question stats

  • viewed: 50227
  • replies: 13
  • date asked: Nov 22 '05
BYTES.COM © 2020 About Bytes | Advertise on Bytes | Privacy Policy | Manage Cookies
Sitemap | PostgreSQL Database Answers Sitemap | PostgreSQL Database Insights Sitemap

Follow us to get the Latest Bytes Updates