Re: Can a function determine whether a primary key constraint

Rob Richardson wrote:
> Greetings!
>
> I recently joined a company that uses a fairly small PostGres database.
> I have never used PostGres before, but I am familiar with (but not
> expert in) SQL Server. The PostGres database has 90 tables (including

Welcome.

> the one I just added). When the database was originally developed, the
> designer merely created tables to describe the various pieces of the
> system. There was no attempt to use primary or foreign keys to describe
> or enforce relationships. I would like to change this. I would like to
> write a function that would add a column to a table, populate it with
> the number 1 to n (where n is the number of rows in the table), make
> that column the table’s primary key, create a sequence beginning with
> n+1, and give the new column a default of nextval(‘new_sequence’). All
> of this is, if I understand things correctly, straightforward. But what

I'd use a slightly different approach, basically ('x' and 'y' are place
holders):

BEGIN;
ALTER TABLE x ADD COLUMN x_id SERIAL NOT NULL PRIMARY KEY;
-- ALTER TABLE x ADD CONSTRAINT x_y_id_fk FOREIGN KEY (y_id) REFERENCES
y (y_id) MATCH FULL ON UPDATE CASCADE -- and maybe: ON DELETE CASCADE;
UPDATE x SET x_id = DEFAULT;
COMMIT; -- after you checked the results

How to determine whether a table has a PK was already explained.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //