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

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

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

Только сел за 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);

Новые блоги и статьи Все статьи Все блоги /
Kanban или Scrum - что выбрать? EggHead 27.04.2025 Kanban и Scrum — уже много лет удерживают лидирующие позиции среди гибких подходов. Руководители проектов и команды разработчиков то и дело сталкиваются с дилеммой: какой из этих двух методов выбрать. . .	Кастомные Middleware на C# в ASP.NET Core UnmanagedCoder 27.04.2025 Разработка веб-приложений сегодня мало напоминает монолитное программирование прошлых лет. На смену громоздким блокам кода пришла модульная архитектура, где каждый компонент выполняет строго. . .	Анализ и линтинг кода JavaScript: ESLint, Prettier и JSHint run.dev 26.04.2025 JavaScript прошёл долгий путь от простого языка для анимации веб-страниц до основы современной веб-разработки. С ростом сложности приложений, увеличением кодовых баз и масштабированием команд. . .	Паттерны в Python: Singleton, Factory и Observer py-thonny 26.04.2025 Паттерны проектирования — это проверенные временем решения типовых проблем разработки программного обеспечения. Их история берёт начало с книги "Приёмы объектно-ориентированного проектирования. . . .	Исключения в C#: Stack Overflow, Access Violation и Out of memory stackOverflow 26.04.2025 Исключения в C# — это не только механизм оповещения о проблемах, а целое искусство управления потоком выполнения программы в экстремальных ситуациях. Обычное исключение, например,. . .
Логирование в C# ASP.NET Core с помощью Serilog, ElasticSearch, Kibana stackOverflow 25.04.2025 Помните те времена, когда для анализа проблемы приходилось подключаться к серверу, искать нужный лог-файл среди десятков других и вручную фильтровать тысячи строк в поисках ошибки? К счастью, эти дни. . .	Структура "железный OnKeyUp" вместо антидребезга. Полностью асинхронный счётчик. Hrethgir 25.04.2025 Программа для симуляции схемы - Logisim Evolution В общем какое-то время отвлёкся, так было надо, теперь когда запилю это на verilog и FPGA , досоставлю заявку в ФИПС на полезную модель - не готов. . .	Автоматизация Amazon Web Services (AWS) с Boto3 в Python py-thonny 25.04.2025 Облачные вычисления стали неотъемлемой частью современной ИТ-инфраструктуры, а Amazon Web Services (AWS) занимает лидирующие позиции среди провайдеров облачных услуг. Управление многочисленными. . .	Apache Kafka vs RabbitMQ в микросервисной архитектуре ArchitectMsa 25.04.2025 Современная разработка ПО всё чаще склоняется к микросервисной архитектуре — подходу, при котором приложение разбивается на множество небольших, автономных сервисов. В этой распределённой среде. . .	Параллельное программирование с OpenMP в C++ NullReferenced 24.04.2025 Параллельное программирование — подход к созданию программ, когда одна задача разбивается на несколько подзадач, которые могут выполняться одновременно. Оно стало необходимым навыком для. . .

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