MySQL error 1215, what am I doing wrong?

I am trying to create a database that holds staff information such as their names, timesheets, holidays booked etc and also information about the projects the are carrying out and what companies the projects are for. My code is below:

    CREATE TABLE IF NOT EXISTS tblcompany (
    companyid INT(11) UNSIGNED NOT NULL,
    custfirst VARCHAR(50),
    custlast VARCHAR(50),
    company VARCHAR(50),
    custphone VARCHAR(50),
    custemail VARCHAR(50),
    PRIMARY KEY (companyid),
    INDEX (companyid),
    CONSTRAINT FOREIGN KEY (companyid)
        REFERENCES tblproject (companyid)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tblemployee (
    employeeid INT(11) UNSIGNED NOT NULL,
    employeefirst VARCHAR(50),
    employeelast VARCHAR(50),
    employeephone VARCHAR(50),
    employeeemail VARCHAR(50),
    PRIMARY KEY (employeeid),
    INDEX (employeeid),
    CONSTRAINT FOREIGN KEY (employeeid)
        REFERENCES tbltimesheet (employeeid),
    CONSTRAINT FOREIGN KEY (employeeid)
        REFERENCES tblholiday (employeeid),
    CONSTRAINT FOREIGN KEY (employeeid)
        REFERENCES tblannualleave (employeeid)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tblholiday (
    holidayid INT(11) UNSIGNED NOT NULL,
    employeeid INT(11) UNSIGNED NOT NULL,
    holidayfrom DATE,
    holidayto DATE,
    holidayhalfday BOOLEAN,
    holidayreason VARCHAR(50),
    INDEX (employeeid),
    PRIMARY KEY (holidayid)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tblannualleave (
    annualleaveid INT(11) UNSIGNED NOT NULL,
    employeeid INT(11) UNSIGNED NOT NULL,
    annualleavetaken INT(11),
    annualleaveremain INT(11),
    anuualleavetotal INT(11),
    INDEX (employeeid),
    PRIMARY KEY (annualleaveid)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tblproject (
    projectid INT(11) UNSIGNED NOT NULL,
    projectname VARCHAR(50),
    projecttype VARCHAR(50),
    companyid INT(11) UNSIGNED NOT NULL,
    projectnotes VARCHAR(50),
    PRIMARY KEY (projectid),
    INDEX (projectid),
    CONSTRAINT FOREIGN KEY (projectid)
        REFERENCES tbltimesheet (projectid)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tbltimesheet (
    timesheetid INT(11) UNSIGNED NOT NULL,
    employeeid INT(11) UNSIGNED NOT NULL,
    projectid INT(11) UNSIGNED NOT NULL,
    timesheetdate DATE,
    timesheethours INT(11),
    timesheetnotes VARCHAR(50),
    INDEX (employeeid),
    PRIMARY KEY (timesheetid)
)  ENGINE=InnoDB;

I have been looking around and have tried everything, it is probably something so simple. I have changed all the datatypes to similar ones to see if this would solve the problem butno luck. The error code I get is:

Error Code: 1215. Cannot add foreign key constraint 0.063 sec

CREATE TABLE IF NOT EXISTS tblcompany ( companyid INT(11) UNSIGNED NOT NULL, custfirst VARCHAR(50), custlast VARCHAR(50),
company VARCHAR(50), custphone VARCHAR(50), custemail VARCHAR(50), PRIMARY KEY (companyid), INDEX (companyid),
CONSTRAINT FOREIGN KEY (companyid) REFERENCES tblproject (companyid) ) ENGINE=InnoDB

11:15:57 CREATE TABLE IF NOT EXISTS tblcompany ( companyid INT(11) UNSIGNED NOT NULL, custfirst VARCHAR(50), custlast VARCHAR(50), company VARCHAR(50), custphone VARCHAR(50),
custemail VARCHAR(50), PRIMARY KEY (companyid), INDEX (companyid), CONSTRAINT FOREIGN KEY (companyid) REFERENCES tblproject (companyid) ) ENGINE=InnoDB Error Code: 1215. Cannot add foreign key constraint 0.063 sec

Thank you for looking..

---

Create the table tblproject first before you reference it.

Besides the wrong table order,you need either a primary or unique key on referenced columns.

SQL fiddle

---

I think you will solve your problem creating your tables in the opposite ordersm, otherwise you will have the same issue with tblemployee.

A Foreigh Key needs that the reference table exists already.

---

You cannot create a foreign key when the table it references does not yet exist. You can always create the foreign key later.

You must first create the tables containing the referenced fields, after you create the table with the foreign key.

You could also create first all the tables, and create all foreign keys in a second step.

链接地址: http://www.djcxy.com/p/15132.html

上一篇: SQL Server执行更新需要更多时间

下一篇: MySQL错误1215，我做错了什么？