> And later I decide I *really* wish I had done:
>
> CREATE TABLE something
> (
> id serial ,
> foo_id int4 not null,
>
> foreign key(foo_id) references foo(id) on update CASCADE,
> );
>
>
> How can I update a live database to add the missing relation?
>
> So far I know that I need to:
>
> ALTER TABLE issue_transaction ADD COLUMN foo_id int4 not null;
>
>
> But how do I declare the foreign key and how do I declare the
> ON UPDATE? Is this something that is better done from a
> backup? I've noticed the trigger syntax in a backup isn't
> exactly friendly, and the TOC Entry IDs are numbered, but I
> woudn't know what number to use to create a new one.
Your add column above won't work - postgres 7.2 doesn't have a command for
changing the null status of a column (7.3 does), so you need to do this:
ALTER TABLE issue_transaction ADD foo_id int4;
-- make sure there's no null values in the column, and then do this
-- catalog twiddle to set the column not null
UPDATE pg_attribute SET attnotnull = true WHERE attrelid = (SELECT oid FROM
pg_class WHERE relname = 'issue_transaction') AND attname = 'foo_id';
ALTER TABLE issue_transaction ADD FOREIGN KEY (foo_id) REFERENCES foo(id) ON
UPDATE CASCADE;
And you're done.
Chris
