PDA

View Full Version : Primary Key



InNeedofHelp
06-10-2006, 04:23 PM
When I see examples of creating tables in MySQL, i often see Primary Key used. So i had no idea what it was and researced it...but found nothing out.
For example:


$sql = "create table IF NOT EXISTS Forum
(
UserName varchar(15),
Password varchar(15),
ID int,
Primary Key (ID)
)"

then that is queried to the MySQL database. My question is, what does that Primary Key (ID) thing do? All i know is that it increases the ID value each time the Insert query is called. I think. Can someone please explain it for me?

Thanks in advance.

P.S. - I'm not sure if i wrote that code right either.

djr33
06-10-2006, 07:27 PM
I'm pretty sure the point is that it just gives each a unique value.
By chance, you might have two entries that have, for example, the same password, or maybe even the same username (if that's allowed), and you need to have a sure way to distinguish between the different entries. If each has a unique ID, then that will do it for you.
You don't always need one, but they are helpful in lots of cases, and rarely hurt.

InNeedofHelp
06-10-2006, 07:31 PM
Ok, but my code above will just increase ID with each user entry? so that it will make a table in my MySQL database that looks sort of like the following:

ID------UserName
1 Bob
2 Rich
3 Sue
4 Mike

is that the basic idea of it?

djr33
06-10-2006, 07:40 PM
Sure. I think that will do fine.
I'm not really familiar with the term "primary key", but I believe that's the point of it... I've just seen it in passing.

Twey
06-11-2006, 11:20 AM
The primary key won't auto-increment by default. You need to specify
ID int auto_increment

djr33
06-11-2006, 08:12 PM
Right, that's the crucial piece... sorry I forgot to mention it :)

InNeedofHelp
06-12-2006, 08:02 PM
Wait, so in my code do i need to put this?

ID int,
Primary Key (ID) auto_increment

or do i just need to put

ID int auto_increment

?

Twey
06-12-2006, 08:17 PM
ID int auto_increment,
primary key(ID)

InNeedofHelp
06-12-2006, 09:19 PM
Allright, thanks.

So then, if the auto_increment increases ID with each Insert query, what does Primary Key do?

Twey
06-12-2006, 10:11 PM
Insofar as I know (not a big database guy) just marks it as being the primary, unique, identifying column. The SQL implementation may perform some optimisations to how it runs based on that -- I'm guessing it would usually assign a primary key column itself if none existed.