Thread: FK relationships

On Wed, Jan 12, 2005 at 10:56:00AM -0800, sarlav kumar wrote:

> Is there an easy way to find out the list of tables that have a
> FK relation on a particular table?

You could query the system catalogs, either directly or via the
Information Schema (the latter is available in 7.4 and later).
See the "System Catalogs" and "The Information Schema" chapters
in the documentation.

> Say, if I have a 'customer' table, I would like to get the tables
> that depend on customer and not the tables on which customer depends.

I think this'll work, although I did only trivial testing with it:

SELECT conrelid::regclass, conname
FROM pg_constraint
WHERE confrelid = 'customer'::regclass AND contype = 'f';

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

On Wed, Jan 12, 2005 at 10:56:00AM -0800, sarlav kumar wrote:

> Is there an easy way to find out the list of tables that have a
> FK relation on a particular table?

You could query the system catalogs, either directly or via the
Information Schema (the latter is available in 7.4 and later).
See the "System Catalogs" and "The Information Schema" chapters
in the documentation.

> Say, if I have a 'customer' table, I would like to get the tables
> that depend on customer and not the tables on which customer depends.

I think this'll work, although I did only trivial testing with it:

SELECT conrelid::regclass, conname
FROM pg_constraint
WHERE confrelid = 'customer'::regclass AND contype = 'f';

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

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/

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/