我不断得到:
错误代码:1451,不能删除或更新父行:外键约束在MySQL上失败。
它只出现在“如果存在分支则删除表”和“如果存在人员则删除表”上,但其余部分功能正常。
CREATE DATABASE IF NOT EXISTS Optician;
USE Optician;
DROP TABLE IF EXISTS branches;
CREATE TABLE branches (
branch_id INT PRIMARY KEY,
branch_name VARCHAR(255) NOT NULL,
branch_address ENUM('Cardiff', 'Swansea', 'Newport') NOT NULL,
staff_available BOOLEAN NOT NULL
);
DROP TABLE IF EXISTS staff;
CREATE TABLE staff (
staff_id INT PRIMARY KEY,
staff_name VARCHAR(255) NOT NULL,
staff_role ENUM('Optician', 'Receptionist', 'Technician') NOT NULL,
staff_available BOOLEAN DEFAULT true,
welsh_speaker BOOLEAN DEFAULT false,
branch_id INT,
FOREIGN KEY (branch_id)
REFERENCES branches(branch_id) ON DELETE CASCADE
);
DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
appointment_id INT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL,
appointment_type VARCHAR(255) NOT NULL,
appointment_date DATE NOT NULL,
appointment_time TIME NOT NULL,
staff_id INT NOT NULL,
FOREIGN KEY (staff_id)
REFERENCES staff(staff_id) ON DELETE CASCADE,
UNIQUE (appointment_date, appointment_time, staff_id)
);
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL,
customer_phone VARCHAR(225) NOT NULL,
customer_email VARCHAR(255) NOT NULL,
order_type VARCHAR(255) NOT NULL,
order_status VARCHAR(255) NOT NULL,
order_date DATE NOT NULL,
technician_id INT NOT NULL,
lens_arrival_date DATE,
assembly_start_date DATE,
assembly_completion_date DATE,
FOREIGN KEY (technician_id)
REFERENCES staff(staff_id)
);
1条答案
按热度按时间qacovj5a1#
第一次运行代码时没有问题,因为需要在引用表(具有FK的表)之前创建被引用表。再次运行代码时,必须先删除引用表(具有FK的表),然后才能删除被引用表。
或者,如果数据库包含您希望删除的所有表,则可以删除数据库并再次运行代码。
在真实的世界的aps中,一旦数据库启动并运行,为了修改表而删除表是不常见的,更常见的是使用ALTER表来修改表。