How can I delete a primary or foreign key?

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]

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.

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

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
tiborh@opendiary.com

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

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

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

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!

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

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.

I've just received this and found it useful.

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
aklaver@comcast.net