MySQL is not a primary foreign key

Question

I am a little newbie and I cannot see primary keys as foreign keys. For me, foreign keys are for joining two rows of a table. Therefore, it makes sense to use, for example, the username the user table as a foreign key in the picture table. This means that the image on this line belongs to the specified user. However, it seems that common practice promotes the use of meaningless numbers as primary identifiers. In addition, the foreign key must / must refer to the primary key. What if I do not know the primary key, but I know another unique column, in this case username , how could I get the primary key from another MySQL statement or, alternatively, point the foreign key to a non-primary key key?

Answer 1

In addition, the foreign key must / must refer to the primary key. What if I do not know the primary key, but I know another unique column, in this case the user name, how will I either get the primary key from another MySQL statement, or alternatively, if the foreign key points to a non-primary key?

Yes, if you have another unique key, you can have foreign keys that reference 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 refer to this unique key ( username ), it makes no sense to have a meaningless identifier. You can drop it and make username PRIMARY KEY tables.

(Edit :) There are several points with an automatically increasing primary key for InnoDB tables, even if it is not used as a reference, because the first Primary or Unique index is created by the default clustering index of the table. The primary char field may have performance INSERT for the INSERT and UPDATE , but it is better to execute SELECT .

---

For a discussion of what to use, a surrogate (meaningless, automatically generated) or natural key and different views on this subject, read this: surrogate-vs-natural-business-keys

Answer 2

The reason you use the “meaningless” value for the primary key is because the “meaningful” values ​​tend to change from time to time.

In case of user renaming, you do not need to go and change many rows in other tables. This is why normal practice gives them a meaningless identifier (usually auto-increment).

Answer 3

I think you can have a foreign key for any column (or column) if at the beginning there is an index created with these columns.

Try to execute

 CREATE INDEX user_username_idx ON user(username); 

and then create your foreign key.