On Wed, Jan 12, 2005 at 12:07:16PM -0800, sarlav kumar wrote:

> $1 FOREIGN KEY (uid) REFERENCES customer(id) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> Since $1 is not very clear about the FK relationship that exists
> between the tables, I would like to rename these, or be able to get
> the names of the colums that are FK on customer table.

The constraint description shows the columns: uid in the referencing
table and id in the referenced table. If you want to rename a
constraint, then use ALTER TABLE to drop it and add it back with a
meaningful name:

ALTER TABLE customer_facts DROP CONSTRAINT "$1";

ALTER TABLE customer_facts ADD CONSTRAINT customer_facts_uid_fkey
FOREIGN KEY (uid) REFERENCES customer(id);

See the ALTER TABLE documentation for more information. You might
want to make these changes inside a transaction to avoid race
conditions with other sessions.

In 8.0 a constraint's default name will be table_column_type, so
instead of $1 you'll get customerdata_uid_fkey, etc.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Hi,

Instead of dropping and recreating the FK, I tried to change the conname value pg_constraint table for a particular relation, and that works.

This is what I did:

update pg_constraint set conname='customer_facts_uid_fkey' where confrelid='customer'::regclass and conrelid='customer_facts'::regclass;

conrelid | conname
--------------------------+-------------------------
customer_identity | $1
customer_sec_info | $1
customerdata | customer_uid_fk
customer_facts | customer_facts_uid_fkey

Is this going to cause any trouble in future?

Thanks,
Saranya

Michael Fuhr <mike@fuhr.org> wrote:
On Wed, Jan 12, 2005 at 12:07:16PM -0800, sarlav kumar wrote:

> $1 FOREIGN KEY (uid) REFERENCES customer(id) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> Since $1 is not very clear about the FK relationship that exists
> between the tables, I would like to rename these, or be able to get
> the names of the colums that are FK on customer table.

The constraint description shows the columns: uid in the referencing
table and id in the referenced table. If you want to rename a
constraint, then use ALTER TABLE to drop it and add it back with a
meaningful name:

ALTER TABLE customer_facts DROP CONSTRAINT "$1";

ALTER TABLE customer_facts ADD CONSTRAINT customer_facts_uid_fkey
FOREIGN KEY (uid) REFERENCES customer(id);

See the ALTER TABLE documentation for more information. You might
want to make these changes inside a transaction to avoid race
conditions with other sessions.

In 8.0 a constraint's default name will be table_column_type, so
instead of $1 you'll get customerdata_uid_fkey, etc.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Wed, Jan 12, 2005 at 01:01:17PM -0800, sarlav kumar wrote:

> update pg_constraint set conname='customer_facts_uid_fkey'
> where confrelid='customer'::regclass and conrelid='customer_facts'::regclass;

Note that if a table has multiple constraints then this update will
try to rename all of them to the same name.

> Is this going to cause any trouble in future?

I don't know about this case, but fiddling with the system tables
can cause problems due to subtle relationships you're not aware of,
and it can have catastrophic consequences if you make a mistake.
Avoid it if possible.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org