Re: are foreign keys realized as indexes?

On 09/05/07, Lew <lew(at)nospam(dot)lewscanon(dot)com> wrote:
>
> 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.

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.

Peter.