MySQL: Fails to create table with Foreign Key

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'

2 answers

  • answered 2019-10-08 06:37 Bilal Siddiqui

    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.

  • answered 2019-10-08 07:02 Jervz09

    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(',')