Converting old FK definitions to better ones

From: Ken Williams <ken(at)mathforum(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Converting old FK definitions to better ones
Date: 2003-04-22 21:27:02
Message-ID: 28664CD6-7509-11D7-90B2-003065F6D85A@mathforum.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm using a database that was recently upgraded from 7.1 to 7.3.2, and
I think some of the data definition is leftover from before that. For
instance, there are no PRIMARY KEY columns defined, they're all just
UNIQUE indexes on an 'id' column.

To bring things more up to date (so I can use tools that depend on
sensing the relational structure of the database), I'm doing things
like this:

ALTER TABLE foo ADD PRIMARY KEY (id);
ALTER TABLE foo DROP CONSTRAINT foo_id_key;

It's a little more complicated than that, though, because often the
'id' column is referenced from another table, so I need to go update
that other table's references before I can drop the UNIQUE constraint.
And that's the part I don't quite know how to do. If I have a table
like this:

# \d title
Table "public.title"
Column | Type | Modifiers
-------------+--------------
+--------------------------------------------------
id | integer | not null default
nextval('"title_id_seq"'::text)
object_id | integer | not null
title | text | not null
Indexes: title_pkey primary key btree (id)
Foreign Key constraints: $1 FOREIGN KEY (object_id) REFERENCES
object(id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: title_create_tr,
title_update_tr

what can I do to make the FOREIGN KEY here reference the PRIMARY KEY in
the "object" table? Or is there some simpler way to handle this
conversion?

-Ken

Browse pgsql-general by date

  From Date Subject
Next Message P G 2003-04-22 22:59:30 Next PostgreSQL release?
Previous Message Jim_Garrett 2003-04-22 21:17:55 "validating" open-source software