I have a large database (one table has about 1,400,000 records) and about 20
tables. As you can guess there are one or two foreign keys that I really
should enforce. I have put them on and take them off fine. But how do I find
out what Foreign Keys there are?
There does not seam to be a \d command to show this as when I do a "\d
tablename" all I get is a list of triggers with such names as
"RI_ConstraintTrigger_6152777" which is totally meaning less to everyone.
I can find the data in pg_trigger but this is not very well formatted.
I have also noticed that it is quite easy to end up with duplicated and
triplicated foreign key constraints (which seams messy and pointless plus
could slow the database down I spouse)
I reckon a view should be able to do the job. But I can't quite work out the
query required.
I would suggest the following columns would be handy.
name - name of constraint.
table - original table
field - original table field name
foreign_table - foreign table name
foreign_field - foreign field name
onetoone - original field has a unique constraint
manytomany - foreign field does not have a unique constraint
type - full/partial
ordered by table,field would make it easy to read.
For the moment I have removed most of the constraints because they are so
difficult to keep track of but this is not ideal. fortunally the systems
still at a development stage.
Oh and is there any way to find out why a vacuum of my database currently
takes in excess of 3 hours... Plus is 7.3rc2 safe to use or should I stick
with 7.2. (I would not use beta or alpha code but I recone a release
candidate should be safe for most non-critical work) I would like to be able
to drop some columns I added to tables by mistake.
Many thanks for anybodies help
Peter Childs
