我正在尝试为一个简单的在线商店创建一个MySQL模式。我现有的表格如下:
-
user
-用户信息
-
address
-用户地址信息
-
product
-产品信息
-
purchase
-由用户创建和购买的购物图
我使用以下SQL代码创建了这些表:
CREATE TABLE user(
user_id INT AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
middle_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
email VARCHAR(40) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
registered BOOLEAN NOT NULL,
phone VARCHAR(12),
PRIMARY KEY(user_id)
);
CREATE TABLE product(
product_id INT AUTO_INCREMENT,
name VARCHAR(40) NOT NULL UNIQUE,
price DECIMAL(5,2) NOT NULL,
description VARCHAR(100) UNIQUE,
PRIMARY KEY(product_id)
);
CREATE TABLE address(
user_id INT,
address_id INT,
city VARCHAR(15) NOT NULL,
district VARCHAR(20) NOT NULL,
details VARCHAR(50) NOT NULL,
is_default BOOLEAN NOT NULL,
FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE CASCADE,
PRIMARY KEY(user_id, address_id)
);
CREATE TABLE purchase(
purchase_id INT AUTO_INCREMENT,
user_id INT,
total_price DECIMAL(8,2) NOT NULL,
state ENUM('placed', 'paid', 'shipped', 'received', 'completed', 'cancelled') DEFAULT 'placed',
user_name VARCHAR(40) NOT NULL,
full_address VARCHAR(85) NOT NULL,
FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE SET NULL,
PRIMARY KEY(purchase_id)
);
现在,我正试图创建一个最终的表来存储购物图中的单个项目,如下所示:
CREATE TABLE purchase_item(
purchase_id INT,
product_id INT,
product_name VARCHAR(40) NOT NULL,
amount DECIMAL(4,2) NOT NULL,
price DECIMAL(8,2) NOT NULL,
FOREIGN KEY(purchase_id) REFERENCES purchase(purchase_id) ON DELETE CASCADE,
FOREIGN KEY(product_id) REFERENCES product(product_id) ON DELETE SET NULL,
PRIMARY KEY(purchase_id, product_id)
);
但我得到以下错误:
ERROR 1215 (HY000): Cannot add foreign key constraint
如果我按以下方式更新产品的外键,则不会出现任何错误:
FOREIGN KEY(product_id) REFERENCES product(product_id) ON DELETE CASCADE,
我想要
purchase_item
如果相应的
购买
会被删除,但不会在相应的
产品
获取删除。