1
CREATE TABLE EMPLOYEE (
 employeeNumber VARCHAR (15) NOT NULL, 
employeeName VARCHAR (20) NOT NULL, 
department VARCHAR (10) NOT NULL,
 phone INT NOT NULL,
PRIMARY KEY (employeeName, department)

CREATE TABLE DEPARTMENTS( 
department VARCHAR (10) NOT NULL,
 department Code VARCHAR(10) NOT NULL, 
department Name VARCHAR(30) NOT NULL, 
FOREIGN KEY (department)
REFERENCES EMPLOYEE (department)

CREATE TABLE EMAIL_INFO(
 employee Name VARCHAR (20) NOT NULL,
 email VARCHAR (30) NOT NULL, 
FOREIGN KEY (employeeName)
 REFERENCES EMPLOYEE (employeeName)
);
1
  • You got your foreign keys backwards; you'd reference departments primary key from from employees, not the other way around.
    – mustaccio
    Commented Jun 3, 2020 at 15:25

2 Answers 2

-2

You have some problems in your code in mysql.

You can't use column manes with spaces, at least not without backticks like you see in the table departments.

The referenced columns need an index, your table employee has a primary key, but because it is a double column you can not use it. in emploee and department your code also fell short in a parenthesis and semicolon

CREATE TABLE EMPLOYEE (
 employeeNumber VARCHAR (15) NOT NULL, 
 employeeName VARCHAR (20) NOT NULL, 
 department VARCHAR (10) NOT NULL,
 phone INT NOT NULL,
 INDEX (employeeName),
 INDEX (department),
PRIMARY KEY (employeeName, department)
);

CREATE TABLE DEPARTMENTS( 
 department VARCHAR (10) NOT NULL,
 `department Code` VARCHAR(10) NOT NULL, 
 `department Name` VARCHAR(30) NOT NULL, 
 FOREIGN KEY (department)
   REFERENCES EMPLOYEE (department));

CREATE TABLE EMAIL_INFO(
 `employee Name` VARCHAR (20) NOT NULL,
  email VARCHAR (30) NOT NULL, 
 FOREIGN KEY (`employee Name`)
   REFERENCES EMPLOYEE (employeeName)
);
1
  • Thank you very much. I had noticed the missing colon and parentheses. I don't know how I didn't copy it. Your answer is really helpful. Commented Jun 3, 2020 at 17:58
0

From MySQL documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

You need to create corresponding indexes on EMPLOYEE table.

Not the answer you're looking for? Browse other questions tagged or ask your own question.