Обсуждение: Foreign Key Columns And Indices
Just a quick question, when a column of a table is defined to be a foreign key, is it implicitly indexed, or does one still need to explicitly CREATE INDEX? Chris -- Christopher Kings-Lynne Family Health Network (ACN 089 639 243)
At 11:01 5/02/01 +0800, Christopher Kings-Lynne wrote: >Just a quick question, when a column of a table is defined to be a foreign >key, is it implicitly indexed, or does one still need to explicitly CREATE >INDEX? The referenced columns must be either a PK or the set of columns in a Unique constraint. As a result, then will have indexes. Not sure about the referencing table - are there any NOTICES when you create an FK? <Aside> PGSQL implements PK/FK & Unique constraints by using indexes (and rules) at the moment. There is no guarantee that this will always be the case - in fact, one path to rationalizing the constraints system is to implement most features as SQL CHECK constraints: PK: Check( (Select Count(*) from Table Where PKCOLS=PKCOLS) = 1) FK: Check( (Select Count(*) from PK_Table Where PKCOLS=FKCOLS) = 1) etc. This is something I would like to see discussed for 7.2. </Aside> ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
On Mon, 5 Feb 2001, Christopher Kings-Lynne wrote: > Just a quick question, when a column of a table is defined to be a foreign > key, is it implicitly indexed, or does one still need to explicitly CREATE > INDEX? I don't think you can actually declare the column in the table as a foreign key. A foreign key is a column or columns in another table. For a single column in the other table I'm pretty sure that column must be 'not null' and 'unique'. An index - other than to inforce uniqueness (currently how it's done in PostgreSQL?) - has nothing to do with the foreign key. Being a mere mortal - not a demi-god of PostgreSQLness - this could be an over simplification or totally out to lunch. Rod --
On Mon, 5 Feb 2001, Christopher Kings-Lynne wrote: > Just a quick question, when a column of a table is defined to be a foreign > key, is it implicitly indexed, or does one still need to explicitly CREATE > INDEX? The foreign key columns are not currently implicitly indexed but you may wish to index them if you're using a referential action other than no action or restrict.
On Mon, 5 Feb 2001, Philip Warner wrote: > PGSQL implements PK/FK & Unique constraints by using indexes (and rules) at > the moment. There is no guarantee that this will always be the case - in > fact, one path to rationalizing the constraints system is to implement most > features as SQL CHECK constraints: > > PK: Check( (Select Count(*) from Table Where PKCOLS=PKCOLS) = 1) > FK: Check( (Select Count(*) from PK_Table Where PKCOLS=FKCOLS) = 1) There are a couple of problems with this for the fk case. The biggest is that check constraints with subselects won't currently do the correct thing (even if it allowed you to specify them). We could replace the current trigger on insert/update of fk table with a check constraint assuming we made it possible to defer check constraints, but all of the stuff on the pk table won't work that way due to the referential actions and the fact that you need to check after delete on pk rows (which AFAIK we don't currently do for check constraints). The spec gives the check version of the foreign key constraint but it was unfortunate that they also added functionality which then made that insufficient. :(
Stephan Szabo wrote: > > On Mon, 5 Feb 2001, Christopher Kings-Lynne wrote: > > > Just a quick question, when a column of a table is defined to be a foreign > > key, is it implicitly indexed, or does one still need to explicitly CREATE > > INDEX? > > The foreign key columns are not currently implicitly indexed but you may > wish to index them if you're using a referential action other than no > action or restrict. You'd probably want to index them either way, because even "NO ACTION" and "RESTRICT" mean that a scan for existing references is done on updates/deletes to the PK. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com