Help me to understand. How to use SQL to establish relationships between tables:

  • one to one;
  • one to many;
  • much to many;
  • four
    This is the basics. Googling for you books where is all this explained? - zed
  • 1. simply id from one table in a column of another table. One-to-one can provide uniqueness to the desired column in both tables. 2. as well as in 1 case, but without uniqueness in the table where there are "many" records. 3. A separate table with two fields id from the 1st table and id from the 2nd. primary key contains both columns - Mike
  • @Mike Can you give a small example if it's not difficult for you? point number 3 understood. not quite understood point number 1 and number 2 - andrey_KU
  • @Mike if I understand you correctly, communication is the level of theory .. at the SQL level, this is explained by CONSTRAINT, thanks to which we set FOREIGN KEY? if we need a 1 to 1 relationship: then we set the FOREIGN KEY in both tables; if 1 to many, then the table that will have many records relating to 1 record of another table - do we create in it the FOREIGN KEY of the first table? - andrey_KU
  • 3
    All 3 types of communication are provided by the foreign key. the only question is the uniqueness of the column in the table that refers. In the main table, the column referenced must be the primary key, so it is unique. Here is the 2nd table if we make the column also the primary key or build the unqie index - then the connection will be 1k1. it is impossible to put the same id 2 times in one of the tables. If there is no uniqueness, then 1 is many. And yes, in the referencing table we do foreign. For many-many links in the link table, respectively, two foreign looking at different tables - Mike

1 answer 1

For example, there is a user table with a unique ID.
таблица 1 пользователь id primary key

there is a second table with a foreign key to the 1st table
таблица2 направление user_id FOREIGN KEY на id 1 таблицы Должность ...и т.д.
This example of communication 1 to 1

one record from 1 table will correspond to 1 record from the second


connection 1 to many, for example, we need to store which tasks are assigned to the user, the first table is unchanged
таблица 3 работы
work_ID primary key user_id FOREIGN KEY на id 1 таблицы
1 user can be assigned several tasks

many to many connection, for example, we need to store the equipment recorded for a particular user, the first table without changes, add two new tables a table with a list of equipment
таблица 4 оборудование
device_ID primary key Name .....

and table with user equipment
таблица 5 Перечень оборудования
user_ID FOREIGN KEY из 1 таблицы
device_ID FOREIGN KEY из 4 таблицы