Use a join query, rather than separate single-table queries:
SELECT a.*, n.url
FROM articles a, news_sites n
WHERE a.news_site_key = n.news_site_key;
-- Greg Johnson
-----Original Message-----
From: Bennett Haselton [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 30, 2001 3:11 PM
To: [EMAIL PROTECTED]
Subject: bad practice to have a primary key field whose value
changes?
I'm creating a database where one of the tables stores data about news
Web
sites, and I'm using the URL of the site as a primary key field. This
field value might change occasionally. I'm wondering if this is bad
practice, especially since foreign keys in other tables might point to
the
news site table. MySQL doesn't enforce referential integrity, so if I
change the URL of a particular site, then another table in the
database
might have a reference to a row that no longer exists.
So I assume the "right" way to do this would be to use an
auto-incremented
number as the primary key field, and then have other tables refer to
that
as their foreign key. My problem with this is that I like to be able
to
dump the contents of the table and see something meaningful without
having
to refer to other tables. Say I have an "articles" table in the
database,
and one of the fields is a foreign key referring to the news site in
the
"news sites" table where the article was found. If I dump the
contents of
the "articles" table, all that I'll see is a number; then I have to go
look
in the "news sites" table to find out which actual site that number
corresponds to. From this point of view, it's a lot easier just to
use the
news site URL as the key field in the "news sites" table -- then
that's
what can be used as the "foreign key" in the articles table.
What would be ideal would be to use auto-incremented numeric fields as
primary key fields, and then have a special field in each table
designated
as the "user-friendly field". That way, when you want to view the
contents
of a table, the "table viewing" algorithm can take each field marked
as a
foreign key, go to that table, look up the "user-friendly" string for
that
row, and display that instead. This would satisfy the requirements in
both
paragraphs above.
Is there already a way to do this, and if not, which of the two
options
above do people usually use?
-Bennett
[EMAIL PROTECTED] http://www.peacefire.org
(425) 649 9024
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
