Unique field?

[lukeurtnowski]

I saw a table like this

CREATE TABLE IF NOT EXISTS `users` (
`userID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varbinary(250) NOT NULL,
PRIMARY KEY (`userID`,`username`)
);

Because the username will be an email address, it must be UNIQUE, is that why its a PK?

[r937]

what you saw is a disaster

when i snap my fingers, you will awaken, and never include an extra column in an auto_increment PK

[Mittineague]

I think the intent of that CREATE was to have the username field be an INDEX.

IMHO best to not learn from what you see unless from
https://dev.mysql.com/doc/refman/5.5/en/create-index.html

[r937]

[quote=“Mittineague, post:3, topic:190221, full:true”]
I think the intent of that CREATE was to have the username field be an INDEX. [/quote]it cannot be useful in an index search unless the query also specifies an id value

so, not useful at all, because if you know the id value, having the username doesn’t make the id more unique, since it’s already unique by virtue of being first

(myisam tables allow an auto_increment to be subordinate to another column, but that’s not what this case is)

and the downside of a compound index is, it would allow the same username to be assigned to more than one id value

a total fustercluck