On Mon, Jan 17, 2000 at 02:14:16PM -0600, Alan Mead wrote:
: Is it possible to explain or define 'foreign keys' in a couple sentences?
: As they were describe in "The Linux Database" (which was not a book I
: liked), they seemed awefully useful and important for any non-trivial,
: real-world database.
Consider the case of a database for project data, and these tables:
projects
--------
projid
typeid
clientid
description
duedate
cost
price
types
-----
typeid
description
clients
-------
clientid
name
add1
add2
city
state
zip
voice
fax
web
You're using the types and clients tables as lookups based on the typeid
and clientid fields from the records in the projects table. In the projects
table, you store (in typeid) a number the corresponds to a record in the
types table that offers a description of the project (Web site, Firewall, etc).
Similarly, you match up a particular project to a client based on the
clientid field from the records in the projects table.
types.typeid and clients.clientid are said to be "foreign keys" for the
fields by the same name in the projects table. In some DBMS's, it's possible
to enforce referential integrity by creating a "relationship". If you're
careful in your application's code, you don't need it though. They slow
your database down with all of the constraint checking. What do you do
with the above to get all the information about a project? Something like:
SELECT
p.projid, p.description, t.description, c.name, p.duedate, p.cost, p.price
FROM
projects p, types t, clients c
WHERE
p.projid = <id you want> AND
p.typeid = t.typeid AND
p.clientid = c.clientid;
--
Jason Costomiris <><
Technologist, cryptogeek, human.
jcostom {at} jasons {dot} org | http://www.jasons.org/
--
To unsubscribe: mail [EMAIL PROTECTED] with "unsubscribe"
as the Subject.
