MySQL Primary Key row - making an accounts table?

[MythicFrost]

Hi,

I'm making a table in my database "main" called "accounts". I've done that now, what I want to know is what do I do to make a row that auto increments every time a new account is added?

The rows for example are, id; username; password; and e-mail address.
For ID I've put it as "int, length 8, name 'id', default 0, set to auto_increment, and set as a primary key".

Should it be set as an index, primary or unique key? I'm not sure.

Kind Regards

 

[angelwatt]

You're actually describing columns, not rows. Here's a way to create the table with the attributes you seem to want.

CREATE TABLE accounts (
  id INT NOT NULL [URL="http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html"]AUTO_INCREMENT[/URL],
  name VARCHAR(36) NOT NULL,
  password VARCHAR(32) NOT NULL,
  email VARCHAR(64) NOT NULL,
  [URL="http://www.w3schools.com/php/php_mysql_create.asp"]PRIMARY KEY[/URL](id)
);

Depending on how many accounts you're expecting you may want something larger than INT.

 

[CJS7070]

Primary Key should be good.

 

[jpyc7]

You'll probably want "name" column to be unique. Your users aren't going to remember their id numbers.

The id column is used for foreign-key relationships (and will be unique since it is the primary key).

 

[MythicFrost]

Thanks for your replies,

Depending on how many accounts you're expecting you may want something larger than INT.

How high can int go?

You're actually describing columns, not rows. Here's a way to create the table with the attributes you seem to want.

Thanks, I've already done this in phpMyAdmin though, through the GUI not SQL.

You'll probably want "name" column to be unique. Your users aren't going to remember their id numbers.

So I should set it as a unique key? What does that actually do?

 

[jpyc7]

Thanks for your replies,

So I should set it as a unique key? What does that actually do?

The unique key will prevent insertion of the same name twice. Of course, you'll have to handle the error when that happens. Meaning that the insert will fail and it's up to your website to present the appropriate message like: "The name XXX is already in use, please pick another name". It might not always be possible to check that the non-unique name is the cause of the insertion failure. Depends on what other ways an insert can fail, e.g. too long a password, invalid email, etc.

Typically, you'll "validate" the input before you submit to the database to catch these sort of errors. So putting the unique key in the database is a second defense against such an insertion. It's still good to have if you are going to manipulate the database without going through the website. This is very likely to be the case, since you'll get things wrong during website development and need to directly modify the database to clean up mistakes made.

 

[angelwatt]

How high can int go?

See the MySQL references.

Thanks, I've already done this in phpMyAdmin though, through the GUI not SQL.

I was just showing you the attributes you need. You can use phpMyAdmin to make the changes if need be.

 

[MythicFrost]

I was just showing you the attributes you need. You can use phpMyAdmin to make the changes if need be.

All right, thank you

See the MySQL references.

Gotcha ty