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