I think you misunderstand how auto_increment works. Primary keys using
auto_increment are NOT row numbers.
If your table has a primary key that is an auto_increment field then
when you add a row to the table the value of the primary key of the new
row is 1 greater than the max(Value) before the row was added.
Once added the value in the field does not change. Let's say you have
ID V1 V2
1 a b
2 x y
3 x u
4 b a
Now you delete the row with ID = 2. The row where V1=x and V2=u still
has a value of 3 in the ID field.
>From reading the post I think to need to look at some refernces on
handling tree/hierarchie structures in a relational table.
Here are 2 references out of many.
http://www.sitepoint.com/article/hierarchical-data-database
http://www.intelligententerprise.com/001020/celko1_1.jhtml
They should help you understand your 2nd question
" So I need a way to distinguish one leo from the other."
-----Original Message-----
From: David Blomstrom [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 24, 2005 2:31 PM
To: mysql@lists.mysql.com
Subject: Re: Treating Two Fields Like One
--- Peter Brawley <[EMAIL PROTECTED]> wrote:
"As you note, the names [of animal taxons] aren't
guaranteed to be unique, or to stay the same . . .
> One way out is to give every table an
> auto-incrementing integer PK, and
> use those keys, which will never change, to mark
> parent-child relationships.
I wanted to follow up on this. I can easily substitute
integers from my primary key for names, but how do I
substitute them for parents? For example:
ID | NAME | PARENT
10 | Canidae | Carnivora
11 | Canis | Canidae
12 | Vulpes |Canidae
I can easily replace Canis with 11, Vulpes with 12.
But they both have the same family - Canidae, which
translates as 10. I could create a new field and
manually, like this:
ID | NAME | PARENT | PARENTID
10 | Canidae | Carnivora | 9
11 | Canis | Canidae | 10
12 | Vulpes |Canidae | 10
But if I add or delete a row, the numerals in my
primary key will change, messing up the values in
PARENTID.
Along similar lines, I have another question...
Consider the database table code below, which displays
animal names (representing all taxonomic heirarchies)
in a child-parent relationship:
ID | NAME | PARENT
1 | Mammalia | (NULL)
2 | Carnivora | Mammalia
3 | Canidae | Carnivora
4 | Canis | Canidae
5 | leo | Canis
6 | Felidae | Carnivora
7 | Panthera | Felidae
8 | leo | Panthera
Rows 5 and 8 represent identical species names, leo.
If I type http://geozoo/stacks/leo/ into my browser,
it defaults to Mammalia > Carnivora > Canidae > Canis
> leo, rather than the lion, Mammalia > Carnivora >
Felidae > Panthera > leo
So I need a way to distinguish one leo from the other.
Would it be possible to somehow combine my
auto-incrementing primary key with the field Name,
converting leo / leo to 5leo / 8leo?
There are two things I'd have to deal with...
1. I'd need to weed the numerals out of the display,
which should look like this...
<a href="http://geozoo/stacks/leo/";>leo</a>
not this...
<a href="http://geozoo/stacks/8leo/";>8leo</a>
2. The numerals would have to be fluid, as I will be
adding and deleting rows. Thus, the lion could be 8leo
one day and 9leo the next.
I can take this to a PHP forum to learn how to
implement it. But I thought someone on this forum
might tell me if it can be done in the first place.
Thanks.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
