Обсуждение: 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

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

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

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

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

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