Foreign Key Columns And Indices

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

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?

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