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:
> 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(at)opendiary(dot)com