MySQL 麻烦大家帮我看看下面的两张表如何创建?

CREATE TABLE store (
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
manager_staff_id TINYINT UNSIGNED NOT NULL,
address_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (store_id),
UNIQUE KEY idx_unique_manager (manager_staff_id),
KEY idx_fk_address_id (address_id),
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
address_id SMALLINT UNSIGNED NOT NULL,
picture BLOB DEFAULT NULL,
email VARCHAR(50) DEFAULT NULL,
store_id TINYINT UNSIGNED NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
username VARCHAR(16) NOT NULL,
password VARCHAR(40) BINARY DEFAULT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (staff_id),
KEY idx_fk_store_id (store_id),
KEY idx_fk_address_id (address_id),
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
不管先创建哪张表都报"不能添加外键引用",请问我应该如何创建这两张表?求指教。

1个回答

外键的定义必须服从:所有的Table必须是InnoDB型,因为MySQL中只有InnoDB类型的表才支持外键。

建议在 primary key( ...)后加 TYPE=INNODB;

qq_35001005
Z见贤思齐 修改之后,操作一波,再说
4 年多之前 回复
hc1515014471
hc1515014471 这两张表的外键是互相引用的,我应该怎么建?
4 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐