mysql 主外键 策略,MySQL非主外键

I'm a bit of a newbie and I can't get my head around primary keys as foreign keys. To me, foreign keys are meant to connect two rows of a table together. Therefore, it would make logical sense to use the, for example, username of the user table as a foreign key in the picture table. This means that the picture in that row belongs to the specified user. However, it appears that general practice favors using meaningless numbers as primary IDs. Furthermore the foreign key must/should refer to the primary key. What if I don't know the primary key, but I know another unique column, in this case username, how would I either get the primary key from within another MySQL statement, or alternatively have the foreign key point to a non primary key?

解决方案

Furthermore the foreign key must/should refer to the primary key. What if I don't know the primary key, but I know another unique column, in this case username, how would I either get the primary key from within another MySQL statement, or alternatively have the foreign key point to a non primary key?

Yes, if you have another unique key, you can have foreign keys referencing it:

CREATE TABLE user

( userid INT NOT NULL

, username VARCHAR(20) NOT NULL

--- other fields

, PRIMARY KEY (userid)

, UNIQUE KEY (username)

) ENGINE = InnoDB ;

CREATE TABLE picture

( pictureid INT NOT NULL

, username VARCHAR(20)

--- other fields

, PRIMARY KEY (pictureid)

, FOREIGN KEY (username)

REFERENCES user(username)

) ENGINE = InnoDB ;

And if all foreign keys in other tables are referencing this Unique Key (username), there is no point in having a meaningless id. You can drop it and make the username the PRIMARY KEY of the table.

(Edit:)

There are a few points having an auto-incrementing primary key for InnoDB tables, even if it is not used as reference because the first Primary or Unique index is made by default the clustering index of the table. A primary char field may have performance drawbacks for INSERT and UPDATE statements - but perform better in SELECT queries.

For a discussion regarding what to use, surrogate (meaningless, auto-generated) or natural keys, and different views on the subject, read this: