Username as primary key

[Carth]

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:

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:

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:

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?

[DaiLaughing]

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.

[svidgen]

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 ...

[Carth]

So you would use integer keys anyway because they are more efficient?

[criterion9]

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.

[svidgen]

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.

[Carth]

OK thanks.