Question

I get Error code 1215 Cannot add foreign key constraint in MySQL work bench lines 10, 11 & 14. I've tried to correct it and it's still wrong. I'm new to SQL and about to give up.

Create Table Employee(
EmployeeNumber   Int           NOT NULL AUTO_INCREMENT,
FirstName       Char(25)   NOT NULL,
LastName       Char(25)   NOT NULL,
Department       Char(35)   NOT NULL DEFAULT 'Human Resources',
Position       Char(35)   NULL,
Supervisor       Int           NULL,
OfficePhone       Char(12)   NULL,
EmailAddress   VarChar(100) NOT NULL UNIQUE,
CONSTRAINT       EMPLOYEE_PK PRIMARY KEY(EmployeeNumber),
CONSTRAINT       EMP_DEPART_FK   FOREIGN KEY(Department)
                   REFERENCES DEPARTMENT(DepartmentName)
                       ON UPDATE CASCADE,
CONSTRAINT       EMP_SUPER_FK   FOREIGN KEY(Supervisor)
                   REFERENCES EMPLOYEE(EmployeeNumber)
);

ALTER TABLE EMPLOYEE AUTO_INCREMENT = 1
;

Answer 1

If you have any doubts, please give me comment...

DEPARTMENT table must create before Employee table, because employee table has a foreign key of DepartmentName.

Create Table DEPARTMENT(

    DepartmentName VARCHAR(35) NOT NULL,

    CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)

);

Create Table Employee(

    EmployeeNumber   Int           NOT NULL AUTO_INCREMENT,

    FirstName       Char(25)   NOT NULL,

    LastName       Char(25)   NOT NULL,

    Department       Char(35)   NOT NULL DEFAULT 'Human Resources',

    Position       Char(35)   NULL,

    Supervisor       Int           NULL,

    OfficePhone       Char(12)   NULL,

    EmailAddress   VarChar(100) NOT NULL UNIQUE,

    CONSTRAINT       EMPLOYEE_PK PRIMARY KEY(EmployeeNumber),

    CONSTRAINT       EMP_DEPART_FK   FOREIGN KEY(Department)

                    REFERENCES DEPARTMENT(DepartmentName)

                        ON UPDATE CASCADE,

    CONSTRAINT       EMP_SUPER_FK   FOREIGN KEY(Supervisor)

                    REFERENCES EMPLOYEE(EmployeeNumber)

);