到目前为止,它只创建了前3个表:employee、skill、client
CREATE SCHEMA IF NOT EXISTS my_db;
USE my_db;
CREATE TABLE IF NOT EXISTS employee(
id INT UNSIGNED AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
position VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS skill(
code VARCHAR(255),
description VARCHAR(255),
PRIMARY KEY(code)
)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS client(
code VARCHAR(255),
description VARCHAR(255),
PRIMARY KEY(code)
)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS project(
code VARCHAR(255),
role VARCHAR(255) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
description VARCHAR(255),
client.code VARCHAR(255),
PRIMARY KEY(code),
FOREIGN KEY(client.code) REFERENCES client(code),
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS responsibility(
id INT UNSIGNED AUTO_INCREMENT,
description VARCHAR(255) NOT NULL,
project.code VARCHAR(255),
PRIMARY KEY(id),
FOREIGN KEY(project.code) REFERENCES project(code),
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=INNODB;
/* Junction Table */
CREATE TABLE IF NOT EXISTS employee_skill(
employee.id VARCHAR(255),
skill.code VARCHAR(255),
PRIMARY KEY(employee.id, skill.code),
FOREIGN KEY(employee.id) REFERENCES employee(id),
FOREIGN KEY(skill.code) REFERENCES skill(code)
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=INNODB;
/* Junction Table */
CREATE TABLE IF NOT EXISTS employee_client(
employee.id VARCHAR(255),
client.code VARCHAR(255),
PRIMARY KEY(employee.id, client.code),
FOREIGN KEY(employee.id) REFERENCES employee(id),
FOREIGN KEY(client.code) REFERENCES client(code)
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=INNODB;,并在命中具有project外键的第一个表时停止
然后抛出这个错误:
#1103 - Incorrect table name 'client'发布于 2019-10-08 15:02:35
CREATE TABLE IF NOT EXISTS project(
code VARCHAR(255),
role VARCHAR(255) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
description VARCHAR(255),
client_code VARCHAR(255),
PRIMARY KEY(code),
FOREIGN KEY(client_code) REFERENCES client(code)
ON DELETE CASCADE
ON UPDATE CASCADE
)ENGINE=INNODB;删除逗号(,)并将您的client.更改为client_code enjoy(',')
发布于 2019-10-08 14:37:21
由于已存在名为client的表,项目表中的列名client.code导致中断。
必须在反引号中使用列名,如下所示:
`client.code`我建议使用下划线代替,并将其作为client_code,以完全避免句点。
https://stackoverflow.com/questions/58279739
复制相似问题