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/

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

Thank you very much. When I use the query, this is the result I get:


SELECT conrelid::regclass, conname FROM pg_constraint WHERE confrelid = 'customer'::regclass AND contype = 'f';
conrelid | conname
--------------------------+-----------------
customer_identity | $1
customer_facts | $1
customerdata | customer_uid_fk

And when I looked up the \d for the tables, I get this:

$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.

Is it possible to rename the foreign key constraints, by changing them directly in the pg_constraint table?

Thanks again.
Saranya

Michael Fuhr <mike@fuhr.org> wrote:
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/


---------------------------------
Do you Yahoo!?
Yahoo! Mail - now with 250MB free storage. Learn more.