Types of keys in databases?

Question

MySQL

Task: you need to structurally separate all the entities of the database and understand who is responsible for what.

I'm trying to figure out the structure of keys (types), indexes, relationships, but so far the confusion is in my head.

I use DataGrip, DBViewer, dock and sql queries:
1) There is no structuring in DataGrip, only icons. It is clear from them where the key is, where the index is

spoiler

2) In DBViewer, the complete structure, while on the 2nd tab everything (relations, indexes, primary) in the indexes group:

spoiler

3) Document types of indexes:
MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT), and where is the foreign key if it is shown when requesting show indexes?

4) When requesting show indexes from "table";

spoiler

key_name is already written here, and then index_type - key or index entity?

The questions are: how to break it all down into specific entities?

What is the common name for indexes or keys?
ForeignKey is also an index/key?
What is the difference between ForeignKey and Reference?
Is the unique index a separate index or just a unique flag to a normal index?

spoiler

Answer 1

how to break it all down into specific entities?

see below

What is the common name for indexes or keys?

they are synonyms. historically, there are a lot of synonyms in the skle, because some of them are standard skl, and some are chips of a specific subd.

ForeignKey is also an index/key?

no, foreign key != index. index = generally a separate structure that is stored separately from the data itself. foreign key = just a reference to another table, and creating a foreign key is essentially creating a unique constraint (table1.column1, table2.column2). Potentially, the DBMS itself can create an index when creating a foreign key (because it almost always makes sense), but this is already a nuance of a specific implementation

What is the difference between ForeignKey and Reference?

what is "reference"? if you are talking about the design

FOREIGN KEY (ColumnName) REFERENCES TableName(ColumnName)

so it's just a keyword. sql was originally designed as a language in which non-programmers can write database queries, so it is quite verbose.

Is the unique index a separate index or just a unique flag to a normal index?

it's a normal index + uniqueness constraint. and this restriction in the future can be both removed and added (although not everything is so simple with the addition).