Re: question on renaming a foreign key

> >> can anyone suggest a non-nightmarish way for me to do this ?
> > If your tables are setup to "ON UPDATE CASCASE" then you are fine.
> > Just updated the main table and PostgreSQL will take care of the rest.

> I doesn't appear that ALTER TABLE can change constraint characteristics.
> You'd have to drop/recreate, no?

Now that you mention it, I've never tried it or seen it done. Here I what I came up with:

CREATE TABLE foo
(
id text constraint foo_pri_key Primary Key
);

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pri_key" for table "foo"
CREATE TABLE

INSERT INTO foo (id) values('hello');
INSERT 0 1

CREATE TABLE bar
(
id serial constraint bar_pri_key Primary key,
fooid text constraint bar_foo_for_key References foo (id)
);

NOTICE: CREATE TABLE will create implicit sequence "bar_id_seq" for serial column "bar.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pri_key" for table "bar"
CREATE TABLE

INSERT INTO bar (fooid) VALUES ('hello');
INSERT 0 1

update foo SET id = 'goodbye';
ERROR: update or delete on "foo" violates foreign key constraint "bar_foo_for_key" on "bar"
DETAIL: Key (id)=(hello) is still referenced from table "bar".

alter TABLE bar DROP CONSTRAINT bar_foo_for_key;
ALTER TABLE

ALTER TABLE bar ADD constraint new_bar_foo_for_key foreign key (fooid) references foo (id) on
update cascade;
ALTER TABLE

update foo set id = 'goodbye';
UPDATE 1

select * from bar;
id | fooid
----+---------
1 | goodbye
(1 row)

It is nice to see things work so well. :-)

Regards,

Richard Broersma Jr.