Новичок не совсем понимает ключи

@hsad · Регистрация: 24.05.2015

Студворк — интернет-сервис помощи студентам

Только сел за SQL, беру первое же задание.
Необходимо создать 3 таблицы, ввести в них данные.
Собственно создаю:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE students 
(
    student_id SERIAL,
    name CHARACTER VARYING(50) NOT NULL,
    birthday DATE NOT NULL,
    PRIMARY KEY (student_id)
);
INSERT INTO students (name, birthday)
VALUES 
('Федотова Дарья Максимовна', '1995-10-30'),
('Иванов Иван Иванович', '1995-05-27'),
('Петров Сергей Александрович', '1996-11-17'),
('Арустамян Нобель Виленович', '1995-02-26'),
('Достоевский Фёедор Михайлович', '1821-11-11')

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE subjects
(
    subj_id INTEGER NOT NULL,
    subj_name CHARACTER VARYING (100) NOT NULL,
    semester INTEGER NOT NULL,
    PRIMARY KEY (subj_id)
);
INSERT INTO subjects (subj_id, subj_name, semester)
VALUES
(33,'Физика', 1),
(19,'Математический анализ', 1),
(24,'Базы данных', 1),
(44,'Дифференциальные уравнения', 2),
(46,'Обработка многопоточной информации', 2)

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE exams
(
    exam_id INTEGER,
    student_id SERIAL REFERENCES students (student_id),
    subj_id SERIAL REFERENCES subjects (subj_id),
    mark INTEGER NOT NULL,
    exam_date DATE NOT NULL
);
INSERT INTO exams (exam_id, mark, exam_date)
VALUES
(101,4, '2018-12-28'),
(102, 5, '2018-12-28'),
(103, 3, '2018-12-28'),
(104, 4, '2018-12-28'),
(105, 5, '2018-12-28')

В таблице exams необходимо выполнить условие: чтобы значения поля exam_id были больше значений поля subj_id.
Получаю ошибку:

SQL
1
2
ERROR:  ОШИБКА:  INSERT или UPDATE в таблице "exams" нарушает ограничение внешнего ключа "exams_subj_id_fkey"
DETAIL:  Ключ (subj_id)=(1) отсутствует в таблице "subjects".

Если в таблице subjects заменить subj_id тип int на serial, то всё работает, но значения у exam_id и subj_id получаются естественно одинаковыми. Ключами хотел связать таблицы, но видимо не понимаю, как они в точности работают.

@grgdvo · 26.10.2018, 18:04

Ключи - это про целостность данных! Когда таблицы связаны по ключам Primary/Foreign keys, то поле на стороне Foreign не может принимать других значений кроме тех, которые есть на стороне Primary. Есть всякие допущения целостности, но основной смысл именно такой.
Foreign поля типа serial, конечно, не противоречит синтаксису, но смысл этой записи неясен. Не всякий раз последовательности под SERIAL на стороне primary и foreign будут совпадать. Обычно они указываются явно, поэтому и нет смысла их делать SERIAL. SERIAL разных таблиц ни как не свяжутся по одинаковым значениям последовательности.
Доп. ограничения на значения полей таблицы могут накладываться через CHECK.
Хотя не ясен смысл этого

Сообщение от hsad

В таблице exams необходимо выполнить условие: чтобы значения поля exam_id были больше значений поля subj_id.

Зачем?? Логичнее было бы сделать exam_id Primary key.

Во всех INSERT нужно добавить поля внешних ключей и значения для них в записях, причем такие, чтобы все вместе они представляли связанную информацию
Студент Федотова ..., дата рождения 1995-10-30, сдавала экзамен по предмету Информатика (1-ый семестр) в дату 26.10.18 и получила отметку 5

SQL
1
INSERT INTO students (name, birthday) VALUES ('Федотова Дарья Максимовна', '1995-10-30');

здесь стал известен student_id, например 111

SQL
1
INSERT INTO subjects (subj_id, subj_name, semester) VALUES (33,'Информатика', 1);

SQL
1
INSERT INTO exams (student_id, subj_id, mark, exam_date) VALUES (111, 33, 5, '2018-10-26');

здесь стал известен exam_id, например 234

@hsad · 28.10.2018, 21:14 **[ТС]**

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

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

Но когда данных много, это же.. попа?

@hsad · 28.10.2018, 21:27 **[ТС]**

В общем, с таким в третьей таблице:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TABLE EXAMS
(
    exam_id INTEGER NOT NULL,
    student_id INTEGER REFERENCES students (student_id),
    subj_id INTEGER REFERENCES subjects (subj_id),
    mark INTEGER,
    exam_date DATE
);
INSERT INTO exams (exam_id, student_id, subj_id, mark, exam_date)
VALUES 
(101, 10, 33, 4, '2018-12-28'),
(201,10, 19 , 4, '2019-01-10'),
(301,10, 24 , 5, '2018-01-15'),
(401,10, 44 , 4, '2019-06-12'),
(501,10, 46 , 5, '2018-06-19'),
(102, 11, 33, 4, '2018-12-28'),
(202, 11, 19 , 3, '2019-01-10'),
(302,11, 24 , 3, '2018-01-15'),
(402,11, 44 , 4, '2019-06-12'),
(502,11, 46 , 5, '2018-06-19'),
(103, 12, 33, 4, '2018-12-28'),
(203,12, 19 , 4, '2019-01-10'),
(303,12, 24 , 4, '2018-01-15'),
(403,12, 44 , 4, '2019-06-12'),
(503,12, 46 , 4, '2018-06-19'),
(104, 13, 33, 5, '2018-12-28'),
(204,13, 19 , 3, '2019-01-10'),
(304,13, 24 , 3, '2018-01-15'),
(404,13, 44 , 5, '2019-06-12'),
(504,13, 46 , 4, '2018-06-19'),
(105, 14, 33, 5, '2018-12-28'),
(205,14, 19 , 5, '2019-01-10'),
(305,14, 24 , 5, '2018-01-15'),
(405,14, 44 , 4, '2019-06-12'),
(505,14, 46 , 5, '2018-06-19')

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

@grgdvo · 29.10.2018, 05:02

Сообщение от hsad

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

Нет, это всего лишь ограничение целостности, заданное Вами, как разработчиком базы данных.
Автоматически ничего не забирается, но автоматически делается проверка того, что связываете.

@hsad · 29.10.2018, 14:49 **[ТС]**

Не могли бы Вы проверить запросы? Они есть в задании.
1) Напишите запрос, выполняющий вывод списка предметов обучения в порядке возрастания отводимых на предмет часов. Поле семестра в выходных данных должно быть первым, за ним должны следовать имя предмета обучения и идентификатор предмета.

SQL
1
SELECT semester, subj_name, subj_id  FROM subjects ORDER BY hours;

2) Напишите запрос для определения количества студентов, сдававших каждый экзамен.
У меня два варианта.
1

SQL
1
2
SELECT student_id, COUNT (mark) AS "Сколько экзаменов сдал" FROM exams WHERE mark IS NOT NULL
GROUP BY student_id;

2

SQL
1
2
3
SELECT student_id, COUNT (mark) AS "Сколько экзаменов сдал" FROM exams
GROUP BY student_id
HAVING COUNT(mark)>=5;

3) Напишите запрос, который по таблице MARKS позволяет найти минимальные оценки каждого студента и который выводит их вместе с идентификатором студента.

SQL
1
SELECT student_id, MIN(mark) AS "Минимальный балл" FROM exams GROUP BY student_id;

4) Напишите запрос, который выполняет вывод суммы баллов всех студентов для каждой даты сдачи экзаменов и представляет результаты в порядке убывания этих сумм.

SQL
1
2
3
SELECT exam_date, SUM(mark) AS "Сумма баллов" FROM exams 
GROUP BY exam_date
ORDER BY SUM(mark) DESC

@grgdvo · 29.10.2018, 15:23

1) Ok
2) В задании сказано: определение количества студентов, сдававших каждый экзамен. Я бы на выходе ожидал бы таблицу с
SELECT exam_id, COUNT(student_id)
3) Сказано же, ... который по таблице MARKS ... Где-то у вас должна быть в схеме данных таблица MARKS, вот с ней и работать
4) Ok

@hsad · 29.10.2018, 16:29 **[ТС]**

В 3) опечатка должна быть, по таблице exams все-таки.
А со 2) не получается. Я бы хотел на выходе иметь одно единственное число тех студентов, кто сдал все экзамены (таких 4). У меня же он отображает id экзамена (допустим 101) в одном столбце, а в другом количество сдавших этот самый экзамен.

SQL
1
2
SELECT exam_id, COUNT (student_id) FROM exams WHERE mark IS NOT NULL
GROUP BY exam_id

@grgdvo · 30.10.2018, 02:03

2) Да, формулировка немного с "подковыркой", сразу смысл не угадывается.

SQL
1
2
3
SELECT student_id, COUNT(exam_id) FROM exams WHERE mark IS NOT NULL
GROUP BY student_id
HAVING COUNT(exam_id)=(SELECT COUNT(*) FROM subjects);

@hsad · 30.10.2018, 12:44 **[ТС]**

Вложенный запрос? Будет что почитать на досуге.

SQL
1
2
3
SELECT student_id, COUNT(exam_id) FROM exams WHERE mark IS NOT NULL
GROUP BY student_id
HAVING COUNT(exam_id)=(SELECT COUNT(*) FROM subjects);

Новые блоги и статьи Все статьи Все блоги /
Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .	Запрет удаления строк ТЧ документа при определенном условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .	Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .
Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .	Знаешь почему 90% людей редко бывают счастливыми? kumehtar 14.04.2026 Потому что они ждут. Ждут выходных, ждут отпуска, ждут удачного момента. . . а удачный момент так и не приходит.	Фиксация колонок в отчете СКД Maks 14.04.2026 Фиксация колонок в СКД отчета типа Таблица. Задача: зафиксировать три левых колонки в отчете. Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка) / / . . .

@grgdvo 1267 / 980 / 385 Регистрация: 02.09.2012 Сообщений: 3,027
	29.10.2018, 15:23
	1) Ok 2) В задании сказано: определение количества студентов, сдававших каждый экзамен. Я бы на выходе ожидал бы таблицу с SELECT exam_id, COUNT(student_id) 3) Сказано же, ... который по таблице MARKS ... Где-то у вас должна быть в схеме данных таблица MARKS, вот с ней и работать 4) Ok 0