我收到"外键约束失败"错误，但找不到源

Question

我有四个表来表示硬件存储：

PRAGMA foreign_keys = 1;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS tools;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
name PRIMARY KEY NOT NULL
);
CREATE TABLE tools (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tool_name TEXT NOT NULL UNIQUE,
tool_price DECIMAL(5, 2),
tool_department VARCHAR(250),
FOREIGN KEY (tool_department) REFERENCES departments(name) ON DELETE CASCADE
);
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL UNIQUE,
last_name TEXT NOT NULL UNIQUE,
phone_number INTEGER UNIQUE NOT NULL
);
CREATE TABLE transactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER,
customer_first_name TEXT NOT NULL,
customer_last_name TEXT NOT NULL,
customer_phone_number INTEGER NOT NULL,
tool_purchased TEXT NOT NULL,
item_quantity INTEGER NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
FOREIGN KEY (tool_purchased) REFERENCES tools(tool_name) ON DELETE CASCADE,
FOREIGN KEY (customer_first_name) REFERENCES customers(first_name) ON DELETE CASCADE,
FOREIGN KEY (customer_last_name) REFERENCES customers(last_name) ON DELETE CASCADE,
FOREIGN KEY (customer_phone_number) REFERENCES customers(phone_number) ON DELETE CASCADE
);

我正试图将这些数据插入我的表中：

INSERT INTO tools(tool_name, tool_price, tool_department)
VALUES
('Snow shovel', 16.50, 'Home & Garden'),
('Work light', 29.99, 'Electrical'),
('Wheelbarrow', 89.99, 'Home & Garden'),
('Pipe Wrench', 18.99, 'Plumbing'),
('Pipe Cutter', 36.36, 'Plumbing'),
('Rake', 15.45, 'Home & Garden');
INSERT INTO customers(first_name, last_name, phone_number)
VALUES
('John', 'Smith', 1111111111),
('Jane', 'Doe', 2222222222);
INSERT INTO transactions(customer_id, customer_first_name, customer_last_name, customer_phone_number, tool_purchased, item_quantity)
VALUES
(1, 'John', 'Smith', 1111111111, 'Work light', 1),
(1, 'John', 'Smith', 1111111111, 'Pipe Cutter', 2),
(2, 'Jane', 'Doe', 2222222222, 'Snow shovel', 3),
(2, 'Jane', 'Doe', 2222222222, 'Work light', 1),
(2, 'Jane', 'Doe', 2222222222, 'Pipe Wrench', 1),
(2, 'Jane', 'Doe', 2222222222, 'Pipe Cutter', 1),
(1, 'John', 'Smith', 1111111111, 'Wheelbarrow', 3);

然而，我得到了这些错误回来：

Error: near line 1: FOREIGN KEY constraint failed
Error: near line 15: FOREIGN KEY constraint failed

Answer 1

您还没有在表departments中定义列name的类型！必须是VARCHAR(250)，如表tools(FK(中所示。

CREATE TABLE departments (
name VARCHAR(250) PRIMARY KEY NOT NULL
);

更正部门表并在其中插入部门，以便将工具引用到此部门。您不能将工具引用到尚不存在的部门。另请阅读有关列类型TEXT和VARCHAR的文档。