MySQL non primary foreign key

Question

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?

Solution 1

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: surrogate-vs-natural-business-keys

Solution 2

The reason that you use a "meaningless" value for a primary key, is that "meaningful" values have a tendency to change from time to time.

In the case of a user being renamed, then you don't want to have to go and change many rows in other tables. This is why it's normal practice to give them a meaningless ID (typically auto-incrementing).

Solution 3

I think you can have the foreign key point to any column (or columns), if there is an index created with those columns at the beginning.

Try executing

CREATE INDEX user_username_idx ON user(username);

and then creating your foreign key should work.