What's the standard way to determine the number for a primary key?

Question

I'm planning to make a very simple program using php and mySQL. The main page will take information and make a new row in the database with that information. However, I need a number to put in for the primary key. Unfortunately, I have no idea about the normal way to determine what umber to use. Preferably, if I delete a row, that row's key won't ever be reused.

A preliminary search has turned up the AUTOINCREMENT keyword in mySQL. However, I'd still like to know if that will work for what I want and what the common solution to this issue is.

Answer 1

AUTOINCREMENT is what you want. As long as you don't change the table's settings, AUTOINCREMENT will continue to grow.

Answer 2

In MySQL that's the standard solution.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

Answer 3

autoincrement

Answer 4

AUTOINCREMENT is the standard way to automatically create a unique key. It will start at 1 (or 0, I can't remember and it doesn't matter) then increment with each new record added to the table. If a record is deleted, its key will not be reused.

Answer 5

Auto increment primary keys are relatively standard depending on which DBA you're talking to which week.

I believe the basic identity integer will hit about 2 billion rows(is this right for mySQL?) before running out of room so you don't have to worry about hitting the cap.

Answer 6

AUTO_INCREMENT is the common choice, it sets a number starting from 1 to every new row you insert. All the work of figuring out which number to use is done by the db, you just ask it back after inserting if you need to ( in php you get it by callin mysql_last_insertid I think )

Answer 7

Unless you have an overriding reason to generate your own PK then using the autoincrement would be good enough. That way the database manages the keys. When you are inserting a row you have to leave out the primary key column.

Say you have a table table = (a, b, c) where a is the primary key then the insert statement would be

insert into table (b, c) values ('bbb', 'ccc')

and the primary key will be auto inserted by the databse.

Answer 8

For something simple auto increment is best. For something more complicated that will ultimately have a lot of entries I generate a GUID and insert that as the key.