MySQL Primary Key row - making an accounts table?

Discussion in 'Web Design and Development' started by MythicFrost, Mar 23, 2010.

  1. MythicFrost macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #1
    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
     
  2. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    You're actually describing columns, not rows. Here's a way to create the table with the attributes you seem to want.

    Code:
    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.
     
  3. CJS7070 macrumors 6502a

    CJS7070

    Joined:
    Dec 10, 2008
    Location:
    Chicago, IL
  4. jpyc7 macrumors 6502

    Joined:
    Mar 8, 2009
    Location:
    Denver, CO
    #4
    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).
     
  5. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #5
    Thanks for your replies,
    How high can int go?
    Thanks, I've already done this in phpMyAdmin though, through the GUI not SQL.
    So I should set it as a unique key? What does that actually do?
     
  6. jpyc7 macrumors 6502

    Joined:
    Mar 8, 2009
    Location:
    Denver, CO
    #6
    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.
     
  7. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #7
    See the MySQL references.

    I was just showing you the attributes you need. You can use phpMyAdmin to make the changes if need be.
     
  8. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #8
    All right, thank you :)
    Gotcha ty :)
     

Share This Page