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

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

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

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

SQL

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

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

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

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);

@hsad 0 / 0 / 0 Регистрация: 24.05.2015 Сообщений: 83
	28.10.2018, 21:14 [ТС]	3
	Во всех INSERT нужно добавить поля внешних ключей и значения для них в записях, причем такие, чтобы все вместе они представляли связанную информацию Т.е. обязательно указывать те же самые значения и в "дочерней" таблице? Я полагал, что делая внешний ключ, дочерняя таблица автоматически получает данные первичного ключа и забирает их к себе. Но когда данных много, это же.. попа? 0

@grgdvo 1251 / 967 / 382 Регистрация: 02.09.2012 Сообщений: 2,989
	29.10.2018, 05:02	5
	Сообщение от hsad Я полагал, что делая внешний ключ, дочерняя таблица автоматически получает данные первичного ключа и забирает их к себе. Нет, это всего лишь ограничение целостности, заданное Вами, как разработчиком базы данных. Автоматически ничего не забирается, но автоматически делается проверка того, что связываете. 1

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