Re: Altering live databases

> 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