MySQL: Fails to create table with Foreign Key

Question

So far, it only creates the first 3 tables: 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;

And stops when it hits the first table with a foreign key which is project

Then throws this error:

#1103 - Incorrect table name 'client'

Don't put dots in your columns names, that's just asking for trouble. If you _must_, quote them with backticks, eg `\`client.code\`` but I'd be more inclined to use an underscore, eg `client_code` — Phil, Oct 08 '19 at 03:25
Possible duplicate of [Is there a “not allowed” character list for mysql table's column names?](https://stackoverflow.com/questions/32901251/is-there-a-not-allowed-character-list-for-mysql-tables-column-names) — Phil, Oct 08 '19 at 03:28
echoing Phil.... `client.code` is invalid as unquoted identifier (i.e. column name). If we include a dot character in a column name, then the column name must be properly escaped. Default way to do that in MySQL is to enclose the identifier in backtick characters *every* place the identifier is referenced. (If sql_mode includes ANSI_QUOTES then we could also use Oracle-style double quote characters.) Again echoing Phil, unless there is some specific reason you need to use a dot character in a column name, then don't do it. And use an underscore as a separator character instead. — spencer7593, Oct 08 '19 at 04:48
Thank you all, I fixed the issue. If anyone can put in their comments as answer, I'd be glad to upvote and accept. Cheers! — k_rollo, Oct 08 '19 at 05:44

Bilal Siddiqui · Answer 1 · 2019-10-08T06:42:17.320

2

Column name client.code in project table is causing a break beacuse there is already a table named client.

You must use column name in backquotes like:

`client.code`

I would suggest to use an underscore instead and have it as client_code to avoid periods completely.

edited Oct 08 '19 at 06:42

answered Oct 08 '19 at 06:37

Bilal Siddiqui

3,579
1
13
20

score 2 · Accepted Answer · edited Oct 08 '19 at 07:06

2

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;

remove the comma(,) and change your client. to client_code enjoy(',')

edited Oct 08 '19 at 07:06

Phil

157,677
23
242
245

answered Oct 08 '19 at 07:02

Jervz09

121
9

2

Nice, nobody else noticed the commas :D – Phil Oct 08 '19 at 07:05

MySQL: Fails to create table with Foreign Key

2 Answers2