At 02:59 PM 12/20/2004, Ranando King wrote:

>Think of it like *before* and *after* checking on the data.
>
>Indexes check the data *after* the data is already in the table.

Ummn, actually, no in firebird the checking is done exactly the
same way in the two cases.

>They sort
>the information so that you can retrieve the rows in some particular order,
>but they only work on rows currently written to the table.

Firebird indexes are b-tree variants, independent of the stored data, and
are populated as part of the data storage operation. They're much more
effective for locating data than for returning data in a particular order.

>Constraints check the data *before they even reach the table. They check the
>data that is about to be written to make sure that it's ok to write. If the
>data doesn't make sense to the constraint, then it would likely break an
>index so the constraint rejects the data.

Actually, in Firebird the information necessary to accept or reject
the value is in the index, so the check is done when the value is
stored in the index. All the storing - data and index - is done in
buffers and the data is tagged with the transaction id so it can be
undone in the case of serious failures. If the index insert gets an
error, the transaction undoes its insert into data itself. Normally
none of the changes go to disk until it's all checked out, but it
can and the system is set up to bring back the incorrect entry for
removal. Other transactions don't see the new record because its
marked with the transaction id of an uncommitted transaction.

>Foreign keys are constraints. They check the data being written to the table
>to make sure that the data in some collection of fields matches the data in
>some other collection of fields in a different table, all before the data is
>written to the table.

No, not exactly. See above.

> Primary key and Unique are constraints that make sure
>the data in that collection of fields is unique for each row in the data.

Yes, and they do it through indexes.

>The reason a Foreign Key must connect to a Primary key and not just another
>index is because Primary keys are guaranteed to maintain referential
>integrity, that is, there's a 1-to-1 relationship between Primary key values
>and associated rows.

Except that in a multi-versioning system like firebird the index
does allow duplicates as long as every active transaction sees only
one record with each version. Takes a bit of bookkeeping, but it
works.

>I'm not sure about this, but I think the
>difference between a Primary key and a Unique Index is that the Primary key
>is the index that the database knows it can depend on for identifying
>records, hence it doesn't have to track an unpredictable number of column
>combinations just to know which record to grab when doing reference checks.

Nope. Either type of index is equally reliable.

>It's about making the architecture more efficient and effective.

It's about complying with the SQL standard which says that the
referenced fields must be subject to a PRIMARY KEY or UNIQUE constraint.

Regards,


Ann