| Subject | Re: [firebird-support] Foreign Keys |
|---|---|
| Author | Ranando King |
| Post date | 2004-12-20T19:59:14Z |
Think of it like *before* and *after* checking on the data.
Indexes check the data *after* the data is already in the table. 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.
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.
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. Primary key and Unique are constraints that make sure
the data in that collection of fields is unique for each row in the data.
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. Unique indexes do the same thing, which is why the
Primary key IS A Unique index. 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.
It's about making the architecture more efficient and effective. Someone
correct me if I'm wrong...
R.
Indexes check the data *after* the data is already in the table. 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.
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.
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. Primary key and Unique are constraints that make sure
the data in that collection of fields is unique for each row in the data.
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. Unique indexes do the same thing, which is why the
Primary key IS A Unique index. 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.
It's about making the architecture more efficient and effective. Someone
correct me if I'm wrong...
R.
----- Original Message -----
From: Chad Z. Hower
To: firebird-support@yahoogroups.com
Sent: Monday, December 20, 2004 10:32 AM
Subject: RE: [firebird-support] Foreign Keys
:: - the target for a FK must be a constraint (an index won't do).
:: - when you define an unique constraint, an unique index is
:: implicitly created.
If I create a constraint, will the index it creates be used for searches and
sorting? So again.. Now I am REALLY confused.. Whas the difference between a
unique constraint and a unique index? I know Helen replied - and it made a
little sense but now Im more confused than before.
:: - when you define an unique index, you have practically the
:: same effect as defining an unique constraint, minus the
:: ability to have FKs point to it.
Why though? And what difference in them causes this?
Yahoo! Groups Sponsor
ADVERTISEMENT
Yahoo! Groups Links
To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.