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
[EMAIL PROTECTED]
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 //
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
