Может ли внешний ключ действовать как первичный ключ?

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

Вот некоторые из таблиц нашей системы:

user_accounts
students
guidance_counselors

Я хотел бы, чтобы таблица user_accounts содержала идентификаторы (предположительно учетные данные для входа в систему) и пароли как пользователей-пользователей, так и пользователей-консультантов. Короче говоря, первичные ключи таблицы students и guidance_counselors также являются внешним ключом из таблицы user_accounts. Но я не уверен, разрешено ли это.

Другой вопрос: существует таблица student_rec, для которой требуется student_number (которая является user_id в таблице user_accounts) и guidance_counsellor_id (которая также является user_id в user_accounts) для каждой записи. Если оба идентификатора студента и консультанта по направлению из user_accounts table, как мне создать таблицу student_rec? И для дальнейшего использования, как мне вручную записать его как код SQL?

Это прослушивало меня, и я не могу найти какой-либо конкретный или верный ответ на мои вопросы.

Ответ 1

Конечно. Это общий метод, известный как супертипирование таблиц. Как и в вашем примере, идея состоит в том, что одна таблица содержит надмножество сущностей и имеет общие атрибуты, описывающие общий объект, а другие таблицы содержат подмножества этих объектов с определенными атрибутами. Это не похоже на простую иерархию классов в объектно-ориентированном дизайне.

Для вашего второго вопроса одна таблица может иметь два столбца, которые являются отдельными внешними ключами для одной и той же таблицы. Когда база данных создает запрос, она дважды присоединяется к этой другой таблице. Чтобы проиллюстрировать в SQL-запросе (не уверен в синтаксисе MySQL, я не использовал его в течение длительного времени, так что это синтаксис MS SQL специально), вы бы дали этой таблице два разных псевдонима при выборе данных. Что-то вроде этого:

SELECT
    student_accounts.name AS student_name,
    counselor_accounts.name AS counselor_name
FROM
    student_rec
    INNER JOIN user_accounts AS student_accounts
      ON student_rec.student_number = student_accounts.user_id
    INNER JOIN user_accounts AS counselor_accounts
      ON student_rec.guidance_counselor_id = counselor_accounts.user_id

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

Ответ 2

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