[Techtalk] Relational databases?

Jenny Brown (was Gable) jenny at bigbrother.net
Thu Sep 6 16:49:08 EST 2001


On Thu, 6 Sep 2001, Kath wrote:

> What is it exactly?  I think I understand the concept, but I'm not sure.
> Is it like an actual symlink or is it more like "Okay, we have a product id 
> for this product, and we will use it through out our database here in our 
> products table, here in our customer shipping table, here in our orders 
> from supplier table and here in the user's shopping cart"?

> Is there a specific command you use in MySQL to make something relational 
> or is it just an idea or practice?

You have the concept correct with the consistent use of IDs.  A database
can enforce 'referential integrity' by not allowing data to be added
that "doesn't make sense" in the context of the rules you set up.  A
database is still considered relational even if it doesn't enforce 
integrity, as long as whatever is using it does so sensibly.

A "primary key" is an id that uniquely identifies one row; in practice
this is things like, customer id, product serial number, etc.  A
"foreign key" is when you use a primary key in another table.  For
instance, I might use customer id in the customer table, with name, 
address, phone, email; in that case it's a primary key.  Then in a
separate table I might list a region, with what customers are in that
region; in that case, the region id would be a primary key, and the
customer id list would be foreign keys (since they refer to a foreign
table).

You can run commands in sql to define primary and foreign keys, and
the behavior when the data is changed. For instance, if I delete
customer #352, I could choose 'cascading delete': all records in
other tables that refer to that same customer would also be deleted.
This way I wouldn't have any orphaned data.

A primary or foreign key is typically called a 'constraint' in the
database; you can find documentation of the commands by searching
for the words primary key and constraint.


In practice not all relational databases actually enforce integrity,
in order to allow for flexibility in programming; but often the
software that creates and uses the data does the integrity checks instead.

Hope this is enough info to get you oriented.  You do have the
concepts for practical use down properly.


Jenny






More information about the Techtalk mailing list