Пример составных первичных ключей в MySQL

Недавно я столкнулся с самим MySQL и темой Composite Primary Keys в MySQL, особенно насколько это полезно и каковы его плюсы и минусы из этого site

Я хотел играть с этим, поэтому я создал три таблицы таким образом:

CREATE TABLE person(
    personId INT(11) NOT NULL,
    personName VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId)
)

CREATE TABLE language(
    languageId INT(11) NOT NULL,
    languageName VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId)
)

CREATE TABLE personLanguage(
    personId INT(11) NOT NULL,
    languageId INT(11) NOT NULL,
    description VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId, languageId),
    FOREIGN KEY (personId) REFERENCES person(personId) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (languageId) REFERENCES language(languageId) ON UPDATE CASCADE ON DELETE CASCADE
)

Я могу вставлять данные в таблицы лиц и языков - прямо, мои вопросы:

Для таблицы personLanguage мне нужно вставить только столбец описания, в то время как другие столбцы автоматически ссылаются или мне нужно вставить значения для двух других столбцов в таблицу personLanguage, а также
Есть ли возможность автоматически обновлять personId и languageId в таблице personLanguage, как только будут вставлены данные в других двух таблицах, насколько я знаю, когда какое-либо обновление/удаление выполняется на личном или языке таблицы он отражает то же самое в двух столбцах в таблице personLanguage
Как получить данные, относящиеся к трем таблицам, например, мне нужно знать, на каком языке говорит человек с personId = 1? Это также прямой запрос с использованием объединений или есть какой-то другой способ сделать, поскольку я использую составные первичные ключи

У меня много вопросов, и я не мог найти целый рабочий пример, чтобы проверить точные плюсы и минусы использования составных первичных ключей. В случае, если кто-то может это сделать, используя мой пример, будет очень полезно.

Я знаю, что у меня есть несколько вопросов, некоторые из которых не имеют никакого смысла, но, пожалуйста, несите меня и проливайте хороший свет на эту тему

Ответ 1

Для таблицы personLanguage мне нужно вставить только описание столбец, в то время как другие столбцы автоматически ссылаются, или я нужно вставить значения для двух других столбцов в manLanguage таблица также

Да, вам нужно будет полностью вставить все три столбца. В противном случае БД не будет знать, какого человека или языка вы пытаетесь связать эту запись.

Есть ли возможность обновить personId и languageId в personLanguage автоматически, как только данные в двух других таблицы вставляются, насколько я знаю, когда выполняется некоторое обновление/удаление в личном или языковом таблицах он отражает то же самое на двух столбцы в персоне Таблица языков

Вы можете сделать это с помощью триггера insert, но это может не иметь никакого смысла. Итак, позвольте сказать, что вы только что ввели новый язык - скажем, французский. Вам не нужно вводить какие-либо значения вообще в таблицу personLanguage, потому что ваши существующие пользователи могут не захотеть получить информацию на французском языке. Такая же ситуация была бы для создания нового человека. У вас может быть много языков. Большинство людей не будут говорить на большинстве языков, поэтому вы не захотите автоматически вводить запись в таблицу personLanguage.

Что касается обновления записей лично и на языке, KEYS не должен меняться. Вот почему вы бы сделали что-то подобное. Как только Бобу или Алисе назначается personId, они являются Идентификатором. Когда французскому назначен langaugeId, всегда должен быть этот язык.

Как получить данные, относящиеся к трем таблицам, например, мне нужно знаете, на каком языке говорит человек с personId = 1? Это также прямой запрос с использованием объединений или есть какой-то другой способ сделать поскольку я использую составные первичные ключи

Ну, это сложный вопрос. Если вы пытаетесь заставить ВСЕ языки personId = 1 говорить, соединение довольно просто.

select pl.personId, l.languageId, l.languageName 
  from personLanguage pl
    join language l on l.languageId = pl.languageId
    where pl.personId = 1

Это становится более сложным, если вы пытаетесь выяснить, на каком языке вы должны общаться с человеком, поскольку есть вероятность, что у человека может не быть определенных лиц. Если вы можете принять нулевые значения, вы можете использовать внешнее соединение, но вы хотите определить запрос, чтобы вы только возвращали один язык.

Ответ 2

Для таблицы personLanguage мне нужно вставить только столбец описания, в то время как другие столбцы автоматически ссылаются или мне нужно вставить значения для двух других столбцов в таблицу personLanguage, а также

Вы можете вставлять значения только в personLanguage, если в ссылочных таблицах есть существующие ключи. Это означает, что вы должны заполнить person и language до вставки значений в personLanguage. Однако, если у вас есть поле NULL canable, вы можете это сделать, но это будет нарушать уникальный составной ключ.
Есть ли возможность автоматически обновить personId и languageId в таблице personLanguage, как только будут вставлены данные в других двух таблицах, насколько я знаю, когда какое-либо обновление/удаление выполняется в любой из человека или языка, он отражает то же самое в двух столбцах в таблице personLanguage

Ограничение, указанное вами (ON UPDATE CASCADE), означает, что при изменении значения ссылки в person или language оно автоматически обновит эти значения в personLanguage. Однако не может быть нарушения ограничения PRIMARY KEY на personLanguage.
Как получить данные, относящиеся к трем таблицам, например, мне нужно знать, на каком языке говорит человек с personId = 1? Это также прямой запрос с использованием объединений или есть какой-то другой способ сделать, поскольку я использую составные первичные ключи

Поскольку это основной пример, на самом деле не было бы необходимости в этом. В расширенной форме вы можете использовать явный JOIN для извлечения данных между таблицами.

Еще несколько мыслей...

Композитные клавиши обычно используются для ссылок в кортежах (или наборах). Это означает, что когда у вас есть составной ключ (col1, col2) на table1, это ссылается на составной ключ (col1, col2) на table2.