Should I make composite foreign keys in the database?

Question

MySQL

1. Is it worth making a composite foreign key of two columns in the database? In what cases is a composite key better/worse than a set of two single ones?
2. Do I understand correctly that the foreign key should be marked as: 1. key 2. as unique
3. How is it customary to set names for keys? "img_region_img_type_uniq" - the name by the name of the columns is it normal?
4. What are the nuances with foreign keys for non-integer types? Like varchar and (tiny)text?

Answer 1

1. foreign keys must be there and those to guarantee referential integrity. There should be a link with a combination of exactly two parameters - that means there should be a link with a combination of a pair of parameters. Better or worse doesn't apply here. Two different fk and one composite have different behavior.
2. for which DBMS? Postgresql following the SQL standard obliges to refer only to a unique field. Mysql also deviates from the standard in this regard and allows you to check a non-unique indexed field.
3. Please refer to the coding standard of the specific project. Usually no one is offended by naming: table name _ field names _fk
4. you can hit the size limit of the indexed field. For postgresql, this is 1/3 of the page size = 2730 bytes by default, for mysql it depends on a bunch of factors.

Answer 2

For muscle:
Without context, this question cannot be answered.
If the bundle is unique OR the search will be performed strictly by two keys, you can save money and put a composite one.
UNIQUE is written when it is unique. You can write INDEX instead of KEY
A question from a series of agreements on a project. There are many standards, there is no one that everyone likes. For example, in my current project, an agreement is accepted: the key is named the same as the column, if the key is composite, then concatenation through the underscore character. For example userId, or userId_categoryId
Use only if the database is not loaded. Integrity through constraints is really expensive.