Re: Altering live databases

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Gregory Brauer" <greg(at)wildbrain(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Altering live databases
Date: 2002-08-08 02:38:26
Message-ID: GNELIHDDFBOCMGBFGEFOKEJFCDAA.chriskl@familyhealth.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

> 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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gregory Brauer 2002-08-08 02:52:37 Re: Altering live databases
Previous Message Christopher Kings-Lynne 2002-08-08 02:32:51 Re: ALTER TABLE follow up