Thread: are foreign keys realized as indexes?

Hi,

I like to keep my pg interface small: Can I replace foreign keys by
using indexes somehow? (This is at least possible for primary key
columns which can be replaced by suitable indexes.)

Thank You
Felix

On 08/05/2007 12:32, Felix Kater wrote:

> I like to keep my pg interface small: Can I replace foreign keys by
> using indexes somehow? (This is at least possible for primary key
> columns which can be replaced by suitable indexes.)

You can do that, but you'll lose the enforcement of referential
integrity, which is what foreign keys give you.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Tue, 08 May 2007 12:48:30 +0100
Raymond O'Donnell <rod@iol.ie> wrote:

> You can do that, but you'll lose the enforcement of referential
> integrity, which is what foreign keys give you.

If I get you right:

There is *no complete* substitute for foreign keys by using *indexes*
since I'd loose the referencial integrity (whereas for unique contraints
there *is* a full replacement using indexes)?

Felix

Am Dienstag, 8. Mai 2007 13:32 schrieb Felix Kater:
> I like to keep my pg interface small: Can I replace foreign keys by
> using indexes somehow?

Not while preserving the semantics.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 08/05/2007 13:14, Felix Kater wrote:

> There is *no complete* substitute for foreign keys by using *indexes*
> since I'd loose the referencial integrity (whereas for unique contraints
> there *is* a full replacement using indexes)?

Here's my understanding....an index is just that (an index) and no more
- it tells PostgreSQL where to look in a table to find a particular row
or set of rows. A foreign key, on the other hand, ensures that
referential integrity is enforced: it enforces the relationship between
rows in a table which refer to rows in another table, depending on how
the foreign key was specified in the first place (cf. the "ON UPDATE...
ON DELETE... etc. clauses).

When you have a foreign key, you can put an index on the foreign key
column in the "child" table for performance reasons, but this *isn't*
the same as the foreign key.

I don't know about the equivalence of unique constraints and indices -
others on the list can answer that.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Tue, 8 May 2007 14:19:12 +0200
Peter Eisentraut <peter_e@gmx.net> wrote:

> > I like to keep my pg interface small: Can I replace foreign keys by
> > using indexes somehow?
>
> Not while preserving the semantics.

I am not bound to indexes, however, wonder if foreign keys itself are
non-atomic functionality. I mean: if foreign keys are based on some
other lower level functionality like indexes or anything else which I
could use as a substitute--in what way ever. Of course, I want to
gain the same (referential integrity etc.).

If foreign keys are, however, something unique which can't be replaced
by any other pg function (I am of course not taking into account things
like multiple queries bound together by transactions...) then I have to
go though it and implement it into my pg interface (looking at the
information_schema: This seems to be quite a bunch of work...).

Thank You
Felix

On Tue, May 08, 2007 at 02:14:54PM +0200, Felix Kater wrote:
> If I get you right:
>
> There is *no complete* substitute for foreign keys by using *indexes*
> since I'd loose the referencial integrity (whereas for unique contraints
> there *is* a full replacement using indexes)?

A unique index is not a "substitute" for a unique constraint, they're
exactly the same thing. If you drop your constraint and create a unique
index, you're back where you started. You neither added nor removed
anything.

On a certain level foreign keys are just triggers, specially coded to
do the work. Yes, you could write your own triggers to do exactly the
same thing, but why bother, when someone has written them for you and
made nice syntax to use them?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, May 08, 2007 at 02:14:54PM +0200, Felix Kater wrote:
>> There is *no complete* substitute for foreign keys by using *indexes*
>> since I'd loose the referencial integrity (whereas for unique contraints
>> there *is* a full replacement using indexes)?

> A unique index is not a "substitute" for a unique constraint, they're
> exactly the same thing. If you drop your constraint and create a unique
> index, you're back where you started. You neither added nor removed
> anything.

Well, actually you added or removed a pg_constraint entry associated
with the index ... but either way it's the unique index that really
does the work of enforcing uniqueness.

            regards, tom lane

On Tue, 8 May 2007 15:54:08 +0200
Martijn van Oosterhout <kleptog@svana.org> wrote:

> A unique index is not a "substitute" for a unique constraint, they're
> exactly the same thing. If you drop your constraint and create a
> unique index, you're back where you started. You neither added nor
> removed anything.

Yes. For this reason I didn't have to implement *both* 'unique
constraints' *and* 'unique indices' in my pg interface.


> On a certain level foreign keys are just triggers, specially coded to
> do the work. Yes, you could write your own triggers to do exactly the
> same thing, but why bother, when someone has written them for you and
> made nice syntax to use them?

My question simply was if I could save coding time... like with 'unique
constaints' and 'indeces', see above. However, for what I have learned
now, 'foreign keys' can *not* be substituted by indeces, so I have to
implement them.

Thanks again.

Felix

Felix Kater <fkater@googlemail.com> writes:
> On Tue, 8 May 2007 15:54:08 +0200
> Martijn van Oosterhout <kleptog@svana.org> wrote:
>> A unique index is not a "substitute" for a unique constraint, they're
>> exactly the same thing.

> Yes. For this reason I didn't have to implement *both* 'unique
> constraints' *and* 'unique indices' in my pg interface.

If you are trying to get away with a dumbed-down subset of SQL, be
prepared for people to refuse to use your tool ;-).

You have to support the unique-constraint syntax because the SQL spec
says so (and people are used to it), and you have to support the
create-index syntax because it gives access to functionality not
available through the constraint syntax.  Unique indexes on expressions
for instance.

            regards, tom lane

Felix Kater wrote:
> I am not bound to indexes, however, wonder if foreign keys itself are
> non-atomic functionality. I mean: if foreign keys are based on some
> other lower level functionality like indexes or anything else which I
> could use as a substitute--in what way ever. Of course, I want to
> gain the same (referential integrity etc.).
>
> If foreign keys are, however, something unique which can't be replaced
> by any other pg function (I am of course not taking into account things
> like multiple queries bound together by transactions...) then I have to
> go though it and implement it into my pg interface (looking at the
> information_schema: This seems to be quite a bunch of work...).

Semantics are not a trivial thing.

Foreign keys are a fundamental semantic of the relational model.  They do not
mean the same thing as an index at all.

I find it strange that anyone would resist the notions of primary and foreign
keys, when they are the basis of the relational model.  Indexes aren't even
part of the relational model - they are a hack to enhance performance.

Sure they ultimately break down to machine instructions, but that's in a whole
different domain of discourse.  A data model is built up from primary keys,
foreign keys and dependent data.  They are fundamental.  They /are/ the
building blocks of your database.  Expressing these molecular concepts in
terms of their constituent atoms will not convey the molecular properties; you
lose a tremendous amount of information.

Just use the syntax that best expresses your structure: PRIMARY KEY and
FOREIGN KEY.

--
Lew

Peter Childs wrote:
> Apart from anything a unique constraint is NOT the same as a unique
> index, as you need a not null constraint on the column as well.

Not true, whichever way 'round you meant it.

For pg unique constraint
<http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2016>
> In general, a unique constraint is violated when there are two or more rows in the table where the values of all of
thecolumns included in the constraint are equal. However, null values are not considered equal in this comparison. That
meanseven in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at
leastone of the constrained columns. This behavior conforms to the SQL standard, 

unique index
<http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>
> When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are
notconsidered equal. 

and further,
> PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The
indexcovers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is
themechanism that enforces the constraint. 

So they are "the same" in pg, and you don't syntactically need a NOT NULL
constraint on the column(s) involved.

--
Lew

On May 13, 2007, at 17:21 , Lew wrote:

> Peter Childs wrote:
>> Apart from anything a unique constraint is NOT the same as a
>> unique index, as you need a not null constraint on the column as
>> well.
>
> Not true, whichever way 'round you meant it.

Technically, the UNIQUE constraint is a logical concept which is
physically implemented in PostgreSQL via a unique BTREE index. Since
there is only one way to implement a UNIQUE constraint in PostgreSQL,
the two concepts are very closely tied. However, say one day
PostgreSQL as a unique GiST index implementation. Then there are two
potentially two physical implementations for the UNIQUE constraint.

>
> For pg unique constraint
> <http://www.postgresql.org/docs/8.1/interactive/ddl-
> constraints.html#AEN2016>
>> In general, a unique constraint is violated when there are two or
>> more rows in the table where the values of all of the columns
>> included in the constraint are equal. However, null values are not
>> considered equal in this comparison. That means even in the
>> presence of a unique constraint it is possible to store duplicate
>> rows that contain a null value in at least one of the constrained
>> columns. This behavior conforms to the SQL standard,

Note here, there is no mention of indexes (a implementation issue):
just the logical constraints.

>
> unique index
> <http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>
>> When an index is declared unique, multiple table rows with equal
>> indexed values will not be allowed. Null values are not considered
>> equal.

Here, they're making the distinction between unique and non-unique
(BTREE) indexes: implementation.

These are subtle points, but worth distinguishing.


Michael Glaesemann
grzm seespotcode net