www.webdeveloper.com
Results 1 to 7 of 7

Thread: Username as primary key

  1. #1
    Join Date
    Mar 2008
    Posts
    55

    Username as primary key

    I'm using MySQL.

    Is it OK to have a username as the primary key instead of an auto-incrementing integer, which is what I've always had in every table? This username must by definition be unique so it seems to serve the purpose: it uniquely identifies a row and determines what the value of all other columns in that row will be.

    Can I just have a users table like this:
    Code:
    CREATE TABLE users (
      name VARCHAR(25) NOT NULL,
      pass VARCHAR(32) NOT NULL,
      ...
      other fields about the user
      ...
      PRIMARY KEY(name)
    );

    If so, maybe I've been overusing unique integer IDs (basically in every table) when they are superfluous. For example, in a many-to-many relationship, I would normally create a second table like this:
    Code:
    CREATE TABLE users_to_something (
      id INT AUTO_INCREMENT NOT NULL,
      user_id INT NOT NULL,
      something_id INT NOT NULL,
      PRIMARY KEY(id)
    );
    but I surely don't need the id field because I could have a composite key:
    Code:
    CREATE TABLE users_to_something (
      user_id INT NOT NULL,
      something_id INT NOT NULL,
      PRIMARY KEY(user_id, something_id)
    );
    Any row must be a unique combination of user_id and something_id. Maybe it's more correct because the first version didn't have that constraint.

    Also, I use MyISAM since it's the default and I've never changed it. Therefore I don't have foreign keys. Do you think it's worth using InnoDB? So that in the table above, integrity would be maintained between all the tables?

  2. #2
    Join Date
    Jul 2009
    Posts
    91
    It's fine to use anything which is unique. Having said that I believe that integer keys are the quickest but that may be offset by ease of use and one less field being needed.

  3. #3
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Also, I use MyISAM since it's the default and I've never changed it. Therefore I don't have foreign keys. Do you think it's worth using InnoDB? So that in the table above, integrity would be maintained between all the tables?
    InnoDB is better suited to most web-tasks, with or without the use of FK's. That in mind, in addition to the fact that using an auto-inc'd PK is just good practice, it will increase INSERT performance on those InnoDB tables (freeing up resources for all other operations). InnoDB also tends to handle concurrent connections better. And, as I understand it, InnoDB handles failure recovery much better than MyISAM (and much more automatically).

    So, I generally avoid MyISAM for any task for which I might need transactions or UPDATES. The overhead of using InnoDB (even if I don't end up using UPDATEs or transactions) is negligible. And of course, I then have the benefit of InnoDB's better data-integrity to comfort me as I try to sleep at night ...
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  4. #4
    Join Date
    Mar 2008
    Posts
    55
    So you would use integer keys anyway because they are more efficient?

  5. #5
    Join Date
    Jan 2009
    Posts
    3,346
    I would avoid using any data that may need to change as part of a composite key. This could cause issues if a cascade on update or delete is not completed properly (I'm not certain but I think MySQL has had some issues with implementing a standard for CASCADE ON clauses). In the event you find data that will not change (as if that were ever the case) such as a social security (which can still be legally changed...though it is difficult) then using it in a composit key would be good practice.

  6. #6
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    So you would use integer keys anyway because they are more efficient?
    In part, yes. But, also because it's good/standard practice.

    ... I also forgot to mention that your JOINs are also best done using an INT/BIGINT auto-inc PK. A lookup on an [BIG]INT is usually a faster than a lookup on a [VAR]CHAR. So, they (ints) are ideal PK's.
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  7. #7
    Join Date
    Mar 2008
    Posts
    55
    OK thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles