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.
They should help you understand your 2nd question
" So I need a way to distinguish one leo from the other."
From: David Blomstrom [mailto:david_blomstrom@stripped]
Sent: Wednesday, August 24, 2005 2:31 PM
Subject: Re: Treating Two Fields Like One
--- Peter Brawley <peter.brawley@stripped> 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
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...
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.
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql