Создание таблицы students и её связи с таблицей courses

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

Давайте еще раз пройдемся по нашему примеру со студентами и курсами. С самого начала. От создания таблиц, до разбора что происходит на каждом этапе. Готовы?

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

Создание таблицы students

Таблица students будет хранить информацию о студентах: их уникальный идентификатор, имя и дату рождения. Мы создадим её с использованием команды CREATE TABLE.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Уникальный ключ для каждой записи
    name TEXT NOT NULL,            -- Имя студента (обязательное поле)
    birth_date DATE                -- Дата рождения студента
);

Что здесь происходит?

1. student_id SERIAL PRIMARY KEY: Это уникальный идентификатор для каждого студента. Тип SERIAL создаёт автоинкрементируемое поле, а PRIMARY KEY гарантирует, что идентификатор будет уникальным для каждой строки.
2. name TEXT NOT NULL: Поле для имени студента. Мы указали NOT NULL, чтобы ни одна запись не могла быть вставлена без указания имени.
3. birth_date DATE: Поле для хранения даты рождения. Здесь тип данных DATE поможет нам работать с датами.

Если бы таблица была как человек, то student_id — это её паспорт, name — очевидное имя, а birth_date — то, что мы иногда скрываем подальше, но для базы данных это важно.

Создание таблицы courses

Теперь создадим таблицу, которая будет хранить информацию о курсах. Она будет включать идентификатор курса, его название и описание.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Уникальный идентификатор курса
    title TEXT NOT NULL,           -- Название курса (обязательное поле)
    description TEXT               -- Описание курса
);

Что здесь происходит?

1. course_id SERIAL PRIMARY KEY: Аналогично, это автоинкрементируемое поле, которое создаёт уникальный идентификатор для каждого курса.
2. title TEXT NOT NULL: Поле для названия курса. Мы указали NOT NULL, так как логично предположить, что у любого курса должно быть имя.
3. description TEXT: Поле для краткого описания курса. Это уже дополнительное поле, которое может быть пустым (без ограничения NOT NULL).

Курс без названия — это как книга без обложки. Но база данных этого не допустит!

Связывание таблицы students с таблицей courses

Теперь представим, что каждый студент может быть записан только на один курс. Для этого мы добавим внешний ключ в таблицу students, который будет ссылаться на идентификатор курса из таблицы courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Уникальный идентификатор курса
    title TEXT NOT NULL,           -- Название курса (обязательное поле)
    description TEXT               -- Описание курса
);

ALTER TABLE students
    ADD COLUMN course_id INT REFERENCES courses(course_id); -- ссылка на курс студента

Что делает course_id INT REFERENCES courses(course_id)?

1. Мы создаём поле course_id в students, которое будет ссылкой на уникальный идентификатор курса (course_id) из таблицы courses.
2. Связь между таблицами обеспечивается ключевым словом REFERENCES.
3. Это означает, что при каждой вставке данных в students значение course_id должно существовать в таблице courses. Если, например, вы попытаетесь записать студента на несуществующий курс, вы получите ошибку.

Заметка: если бы FK был в обратную сторону — courses(student_id), — это означало бы «один студент может вести (или владеть) много курсов», а не «один студент записан на один курс».

Вставка данных в таблицы

Когда таблицы созданы, давайте добавим немного студентов и курсов. Ведь это скучно — сидеть в пустой аудитории, верно?

Добавляем курсы

INSERT INTO courses (title, description) VALUES
('Основы SQL', 'Изучение базового синтаксиса SQL'),
('Реляционные базы данных', 'Понимание реляционных моделей'),
('PostgreSQL для начинающих', 'Установка и настройка PostgreSQL');

Добавляем студентов и записываем их на курсы

INSERT INTO students (name, birth_date, course_id) VALUES
('Alex Lin', '2000-05-10', 1),
('Maria Chi', '1998-02-15', 2),
('Otto Song', '2001-09-25', 3);

Мы указали course_id для каждого студента, связывая его с идентификатором курса в таблице courses.

Проверяем связь между таблицами

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

SELECT
    courses.title AS course_title,
    courses.description AS course_description,
    students.name AS student_name
FROM
    courses
JOIN
    students ON students.course_id = courses.course_id;

Пример результата: Результат запроса:

Мы связали курсы со студентами через внешний ключ — и теперь можем получать связанные данные в одном запросе. Вот так работает реляционная модель!

Резюме

Я надеюсь, что все, что мы только что разобрали было для вас понятно и очевидно. Потому что скоро мы пойдем дальше, и хотелось бы чтобы вы были уверены в своих знания.

2

Задача

SQL SELF, 20 уровень, 0 лекция

Недоступна

Создание таблицы `students`

Создание таблицы `students`

Открыть

2

Задача

SQL SELF, 20 уровень, 0 лекция

Недоступна

Создание таблицы `courses` и внешнего ключа

Создание таблицы `courses` и внешнего ключа

Открыть