CODEWITHSUNDEEP
mysqlsqlsyntaxsyntax-error
Fellow Rémi
Fellow Rémi

Reputation: 157

Error on create mysql tables?

Im trying to create a database in mysql, the first bit of my code works fine but then i get a syntax error on:

CREATE TABLE Project_Staff ( empID INT NOT NULL, projID INT NOT NULL, CONSTRAINT

i dont understand where is the error. Here is my code:

CREATE TABLE Employees (
    empID INT NOT NULL AUTO_INCREMENT,
    empSurname VARCHAR(255) NOT NULL,
    empLastname VARCHAR(255) NOT NULL,
    empJobtitle VARCHAR(255) NOT NULL,
    empLinemanager VARCHAR(255) NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (empID)
) ENGINE=InnoDB;

CREATE TABLE Skills (
    sklID INT NOT NULL AUTO_INCREMENT,
    sklName VARCHAR(255) NOT NULL,
    CONSTRAINT pk_skills PRIMARY KEY (sklID)
) ENGINE = InnoDB;

CREATE TABLE Employees_Skills (
    empskID INT NOT NULL AUTO_INCREMENT,
    empskLevel INT NOT NULL,
    sklID INT NOT NULL,
    empID INT NOT NULL,
    CONSTRAINT fk_employees_skills FOREIGN KEY (sklID) REFERENCES Skills(sklID),
    CONSTRAINT fk_employees_skills_1 FOREIGN KEY (empID)  REFERENCES Employees(empID),
    CONSTRAINT pk_employees_skills PRIMARY KEY (empskID)
) ENGINE = InnoDB;   

CREATE TABLE Project (
    projID INT NOT NULL AUTO_INCREMENT,
    projName VARCHAR(255) NOT NULL,
    projDuration INT NOT NULL,
    projStartdate VARCHAR (255) NOT NULL,
    CONSTRAINT pk_project PRIMARY KEY (projID)
) ENGINE = InnoDB

CREATE TABLE Project_Staff (
    empID INT NOT NULL,
    projID INT NOT NULL,
    CONSTRAINT fk_project_staff FOREIGN KEY (empID) REFERENCES  Employees(empID),
    CONSTRAINT fk_project_staff FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB

CREATE TABLE Skill_For_Project (
    sklreqDuration INT NOT NULL,
    projID INT NOT NULL,
    sklID INT NOT NULL,
    CONSTRAINT fk_skill_for_project FOREIGN KEY (sklID) REFERENCES  Skills(empID),
    CONSTRAINT fk_skill_for_project FOREIGN KEY (projID) REFERENCES Project (projID)
) ENGINE = InnoDB

Upvotes: 2

Views: 142

Answers (2)

echo_Me
echo_Me

Reputation: 37243

  • you have Duplicate key name 'fk_project_staff':

  • Duplicate key name 'fk_skill_for_project':

  • mising empID in skills table. you may interested in Employees(empID) in Skill_For_Project table.

  • You have misses Semicolon at the end of Create Table Statement

here full working code

CREATE TABLE Employees (
empID INT NOT NULL AUTO_INCREMENT,
empSurname VARCHAR(255) NOT NULL,
empLastname VARCHAR(255) NOT NULL,
empJobtitle VARCHAR(255) NOT NULL,
empLinemanager VARCHAR(255) NOT NULL,
CONSTRAINT pk_employees PRIMARY KEY (empID)
) ENGINE=InnoDB;

CREATE TABLE Skills (
sklID INT NOT NULL AUTO_INCREMENT,
sklName VARCHAR(255) NOT NULL,
CONSTRAINT pk_skills PRIMARY KEY (sklID)
) ENGINE = InnoDB;

CREATE TABLE Employees_Skills (
empskID INT NOT NULL AUTO_INCREMENT,
empskLevel INT NOT NULL,
sklID INT NOT NULL,
empID INT NOT NULL,
CONSTRAINT fk_employees_skills FOREIGN KEY (sklID) REFERENCES Skills(sklID),
CONSTRAINT fk_employees_skills_1 FOREIGN KEY (empID)  REFERENCES Employees(empID),
CONSTRAINT pk_employees_skills PRIMARY KEY (empskID)
) ENGINE = InnoDB;   

CREATE TABLE Project (
projID INT NOT NULL AUTO_INCREMENT,
projName VARCHAR(255) NOT NULL,
projDuration INT NOT NULL,
projStartdate VARCHAR (255) NOT NULL,
CONSTRAINT pk_project PRIMARY KEY (projID)
) ENGINE = InnoDB;

CREATE TABLE Project_Staff (
empID INT NOT NULL,
projID INT NOT NULL,
CONSTRAINT fk_project_staff FOREIGN KEY (empID) REFERENCES  Employees(empID),
CONSTRAINT fk_project_staff2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB;

CREATE TABLE Skill_For_Project (
sklreqDuration INT NOT NULL,
projID INT NOT NULL,
sklID INT NOT NULL,
CONSTRAINT fk_skill_for_project FOREIGN KEY (sklID) REFERENCES  Employees(empID),
CONSTRAINT fk_skill_for_project2 FOREIGN KEY (projID) REFERENCES Project(projID)
) ENGINE = InnoDB;

http://sqlfiddle.com/#!2/d75182

Upvotes: 2

Rahul
Rahul

Reputation: 77926

As far I see, there are two issues

  1. You missed a semicolon after create project table

    CREATE TABLE Project ( projID INT NOT NULL AUTO_INCREMENT, CONSTRAINT pk_project PRIMARY KEY (projID) ) ENGINE = InnoDB; <-- Here

  2. Both your constraint names (like fk_project_staff in Project_Staff table and fk_skill_for_project in Skill_For_Project table) are same; Try giving them a different name

Upvotes: 1

Related Questions