CODEWITHSUNDEEP
phpmysqldatabase
Eugene M
Eugene M

Reputation: 48957

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

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.

Upvotes: 4

Views: 550

Answers (7)

Miyaka
Miyaka

Reputation:

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.

Upvotes: 0

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

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.

Upvotes: 6

Champo
Champo

Reputation: 3419

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 )

Upvotes: 1

thismat
thismat

Reputation: 2096

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.

Upvotes: 1

Bill the Lizard
Bill the Lizard

Reputation: 405715

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.

Upvotes: 1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340171

In MySQL that's the standard solution.

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

Upvotes: 19

Thomas Owens
Thomas Owens

Reputation: 116159

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

Upvotes: 4

Related Questions