The identity would make critterid an auto-incrementing number that starts at the value of 1 and gets incremented by one everytime an row insert is done.
Ah, ok. We have that number that increments by one every time we add a new critter, but it isn't the key. It simply keeps count for us of how many critters we own.
In terms of what you have, you've used the name as the primary key, so you don't have to worry about using an AutoNumber as the key. For simple databases, using a character field will probably be ok, but there are a number of things you have to worry about as you scale an application. First off, there is the question of efficiency. For most processors, a number is an atomic value for the cpu. Running through a table trying to find an integer value is far faster than doing lengthy string searches and comparisons.
Hmmm, all I do is sort by critter type, or critter name to match whether a name exists... is that a string search or comparison? (Need to look those up, I think).
The second problem is one of maintenance. Let's say that you at some point need to change the name. Every table that keys off the critter table uses the name as the lookup key. If you change the name in the critter table, you run into the problem that you have to change the lookup key in every table that references the critter table. Worse still, if you are enforcing referential integrity on the foreign keys, you have a chicken-egg situation - you can't change the name in the critter table because it is referenced in other tables - and you can't change the names in the other tables if that name is not already in the critter table.
Ah ok, well since there are no tables to this table, (it's all one table), I see why I don't run into the problem. I have changed names before, but it's simply a change name in the field and then it takes it and all is well.
But I see how that would get complex in the cases of databases that required duplicate names.
To get around these type of issues, many database tables just use a autonumber as the primary key in the table. The downside, of course, is that the key to the data becomes just some number that has no meaning outside of the database context (though some assigned numbers do take on a life of their own).
That's why John said my table was so smart, wow. Because every data bit in the row is dependent upon, or related to the name. Neat.
Thanks for explaining that.
Nightowl >8#