mysql - 如何使一组特定值仅在一行中可用

Question

我想为我拥有的示例项目建立一个数据库。我选择了一所驾驶学校，并使用包括教师、学生、汽车、时间类(class)和预订在内的表格。代码可以在代码部分看到。前四个表，即教师、学生、汽车和时间类(class)都是预订中的外键。

我的问题是我需要这些外键的某些组合才能使用一次。让我澄清一下:- 老师不能在同一时间教两次课- 学生不能在驾驶 time_lessons 中驾驶两次- 同一辆车不能在同一时间使用两次类(class)- 但是，time_lessons 中的时间可以多次使用，前提是您有一名学生、一名老师和一辆尚未预订的汽车。

如何设置？

请参阅下面的代码，了解我到目前为止所做的事情...

DROP DATABASE IF EXISTS c4trafik;
CREATE DATABASE c4trafik;
USE c4trafik; 

CREATE TABLE teachers(
    id INT PRIMARY KEY AUTO_INCREMENT,
    t_fname VARCHAR(20) NOT NULL,
    t_lname VARCHAR(20) NOT NULL,
    auth_lvl INT DEFAULT 3
);

CREATE TABLE pupils(
    id INT PRIMARY KEY AUTO_INCREMENT,
    p_fname VARCHAR(20) NOT NULL,
    p_lname VARCHAR(20) NOT NULL,
    persnr CHAR(10) NOT NULL,
    email VARCHAR(50) DEFAULT NULL,
    telnr VARCHAR(10) DEFAULT NULL,
    to_pay INT DEFAULT 0,
    has_pay INT DEFAULT 0
);

CREATE TABLE cars(
    id INT PRIMARY KEY AUTO_INCREMENT,
    regnr VARCHAR(6) NOT NULL,
    auto_gear BOOLEAN DEFAULT false
);

CREATE TABLE time_lessons(
    id INT PRIMARY KEY AUTO_INCREMENT,
    t_start TIMESTAMP NOT NULL,
    t_end TIMESTAMP NOT NULL,
    les_type VARCHAR(20) DEFAULT 'Driving Lesson'
);

CREATE TABLE bookings(
    teachers_id INTEGER NOT NULL,
    pupils_id INTEGER NOT NULL,
    cars_id INTEGER NOT NULL,
    time_lessons_id INTEGER NOT NULL,
    FOREIGN KEY(teachers_id) REFERENCES teachers(id),
    FOREIGN KEY(pupils_id) REFERENCES pupils(id) ON DELETE CASCADE,
    FOREIGN KEY(cars_id) REFERENCES cars(id),
    FOREIGN KEY(time_lessons_id) REFERENCES time_lessons(id) ON DELETE CASCADE,
    PRIMARY KEY(teachers_id, pupils_id, cars_id, time_lessons_id) 
);

INSERT INTO teachers (t_fname, t_lname, auth_lvl) VALUES
('Ali', 'Alisson', 1),
('Adam', 'Adamsson', 2),
('Noah', 'Noahsson', 3);

INSERT INTO pupils(p_fname, p_lname, persnr, email, telnr, to_pay, has_pay) VALUES
('Jakob', 'Jaboksson', '8702111254', 'pupil1@gmail.com','0704585962', 2800, 2800),
('Hassan','Hassansson', '9504234858' ,NULL,'0704125463',5000,1000),
('Mona','Monasson', '9410118547',NULL, NULL, 10200, NULL);

INSERT INTO cars (regnr, auto_gear) VALUES
('MNS111', false),
('OJS111', true),
('MNF111', false);

INSERT INTO time_lessons (t_start, t_end ) VALUES
('2019-06-10 08:00:00', '2019-06-10 08:40:00'),
('2019-06-10 08:50:00', '2019-06-10 09:30:00'),
('2019-06-10 09:40:00', '2019-06-10 10:20:00'),
('2019-06-10 10:30:00', '2019-06-10 11:10:00'),
('2019-06-10 11:20:00', '2019-06-10 12:00:00'),
('2019-06-10 13:10:00', '2019-06-10 13:50:00'),
('2019-06-10 14:00:00', '2019-06-10 14:40:00'),
('2019-06-10 14:50:00', '2019-06-10 15:30:00'),
('2019-06-10 15:40:00', '2019-06-10 16:20:00');

INSERT INTO bookings(teachers_id ,pupils_id, cars_id, time_lessons_id) VALUES
(1,2,1,1),(2,1,1,1),
(1,2,1,2),(2,1,2,2),
(1,1,1,3),(2,2,1,3);
-- Ska ge error om man duplicerar!!
-- Find main admin
SELECT t_fname, t_lname FROM teachers WHERE auth_lvl = 1;

-- How many manual geared cars?
SELECT COUNT(*) AS manually_geared FROM cars
WHERE auto_gear = false;

-- all booked student
SELECT p_fname, p_lname FROM bookings
INNER JOIN pupils ON pupils.id = bookings.pupils_id
GROUP BY p_lname, p_fname;

-- What time will each student drive?
SELECT p_fname, p_lname, t_start, t_end FROM bookings
INNER JOIN pupils ON pupils.id = bookings.pupils_id
INNER JOIN time_lessons ON time_lessons.id = bookings.time_lessons_id
ORDER BY p_lname, p_fname, t_start;

-- All time_lessons for teacher number 1
SELECT t_fname, p_fname, p_lname, t_start, t_end FROM bookings
INNER JOIN teachers ON teachers.id = bookings.teachers_id
INNER JOIN pupils ON pupils.id = bookings.pupils_id
INNER JOIN time_lessons ON time_lessons.id = bookings.time_lessons_id
WHERE teachers.id = 1;

所以我想知道我是否要改变我的结构？我是否缺少一些代码来使我上面提到的功能起作用。

Answer 1

教师不能教授同一时间的类(class)两次

在 booking 中的 (teacher_id,time_lesson_id) 元组上添加 UNIQUE 约束，例如

CREATE UNIQUE INDEX booking_UX1 ON booking (teacher_id,time_lesson_id)

这将禁止包含重复值组合的行。如果尝试INSERT新行(或UPDATE和现有行)会导致两行具有相同的值，则该语句将失败，并显示错误 1062“重复条目” ' 表示键“。

<小时/>

学生不能在两次驾驶 time_lessons 中驾驶

唯一索引将防止重复:

CREATE UNIQUE INDEX booking_UX2 ON booking (pupil_id,time_lesson_id)

<小时/>

(我的偏好是实体表以单数命名，以命名一行所代表的内容。)