Primary key question with PHPmysql database

[Chimay10]

I know that updating the primary key is taboo, but I have a a newsletter subscription form set up that works perfectly, but I can't figure out how to create the primary key.  I tried using the subscriber email address (because its unique, but in trying to add an update function I realize that the email address may need to be updated.

My question is how do I get a primary key that will work when I'm relying on a form variable to create the record and therefore have no control over assigning one?

Is there a way to have one automatically assigned when a subscriber hits the submit button?

[bregent · Accepted solution]

That's why it's always a good idea to use surrogate keys. You can simply have the database generate a unique id. In MySQL, I think this is a column with the auto_increment attribute. If you need to then obtain that id after insert, php has functions for this (mysql_insert_id() I believe).

[Chimay10]

Bingo.

I wrote:

ALTER TABLE email_list  ADD (      id MEDIUMINT NOT NULL AUTO_INCREMENT,      name CHAR(30) NOT NULL,      PRIMARY KEY (id)  )

I got my primary key column.  thank you so much!!!

[Chimay10]

You be the regent.

Got it handled with the following sql:

ALTER TABLE email_list ADD ( id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL, PRIMARY KEY (id) );

thanks for the guidance.

[bregent]

You're welcome.