From: | "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Can a function determine whether a primary key constraint exists on a table? |
Date: | 2006-10-12 13:17:44 |
Message-ID: | 04A6DB42D2BA534FAC77B90562A6A03D46BE5A@server.rad-con.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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
if the table already has a primary key contraint? A few of them do, but
I believe the designer used them to enforce uniqueness, not to describe
relationships. So I would like my function to check of the target table
has a primary key constraint. If it does, that constraint should be
dropped and a new one added to ensure that the column values are unique.
How can I check for the presence of constraints inside a function?
Also, can someone point me to a web resource that describes the syntax
of PostGres functions? The database I have has several functions that I
can use as examples, but I don't have a reference book.
Thank you very much.
Rob Richardson
RAD-CON INC.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2006-10-12 13:29:04 | Re: more anti-postgresql FUD |
Previous Message | Merlin Moncure | 2006-10-12 13:17:15 | Re: [HACKERS] Database Auditing |