Question

I am coding in MySQL and two of my tables are populating find but the other two "Employee" and "Assignment" won't. I keep getting an error code saying I can't alter a child table and I can't figure out what to fix. Here is my code:

CREATE TABLE DEPARTMENT (

DepartmentName Char(35) NOT NULL,

BudgetCode Char(30) NOT NULL,

OfficeNumber Char(15) Not Null,

DepartmentPhone Char(12) 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)

);

ALTER TABLE EMPLOYEE AUTO_INCREMENT=1;

CREATE TABLE PROJECT(

ProjectID Int NOT NULL AUTO_INCREMENT,

ProjectName Char(50) NOT NULL,

Department Char(35) NOT NULL,

MaxHours Numeric(8,2) NOT NULL DEFAULT 100,

StartDate Date NULL,

EndDate Date NULL,

CONSTRAINT PROJECT_PK PRIMARY KEY(ProjectID),

CONSTRAINT PROJ_DEPART_FK FOREIGN KEY(Department)

REFERENCES DEPARTMENT(DepartmentName)

ON UPDATE CASCADE

);

ALTER TABLE PROJECT AUTO_INCREMENT=1000;

CREATE TABLE ASSIGNMENT (

ProjectID Int NOT NULL,

EmployeeNumber Int NOT NULL,

HoursWorked Numeric(6,2) NULL,

CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID, EmployeeNumber),

CONSTRAINT ASSIGN_PROJ_FK FOREIGN KEY(ProjectID)

REFERENCES PROJECT(ProjectID)

ON UPDATE NO ACTION

ON DELETE CASCADE,

CONSTRAINT ASSIGN_EMP_FK FOREIGN KEY(EmployeeNumber)

REFERENCES EMPLOYEE(EmployeeNumber)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

INSERT Department(DepartmentName, BudgetCode, OfficeNumber, DepartmentPhone)

VALUES('Administration', 'BC-100-10', 'BLDG01-201', '360-285-8100'),

('LEGAL', 'BC-200-10', 'BLDG01-220', '360-285-8200'),

('Human Resources', 'BC-300-10', 'BLDG01-230', '360-285-8300'),

('Finance', 'BC-400-10', 'BLDG01-110', '360-285-8400'),

('Accounting', 'BC-500-10', 'BLDG01-120', '360-285-8405'),

('Sales and Marketing','BC-600-10', 'BLDG01-250', '360-285-8500'),

('InfoSysems', 'BC-700-10', 'BLDG02-210', '360-285-8600'),

('Research and Development', 'BC-800-10', 'BLDG02-250', '360-285-8700'),

('Production', 'BC-900-10', 'BLDG02-110', '360-285-8800')

INSERT Employee(EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress)

VALUES('1', 'Mary', 'Jacobs', 'Administration', 'CEO', NULL, '360-285-8110', '[email protected]'),

('2', 'Rosalie', 'Jackson', 'Administration', 'AdminAsst', '1', '360-825-8120', '[email protected]'),

('3', 'Richard', 'Bandalone', 'Legal', 'Attorney', '1', '360-285-8210', '[email protected]'),

('4', 'George', 'Smith', 'Human Resources', 'HR3', '1', '360-285-8310', '[email protected]'),

('5','Alan', 'Adams', 'Human Resources', 'HR1', '4', '360-285-8320', '[email protected]'),

('6', 'Ken', 'Evans', 'Finance', 'CFO', '1', '360-285-8410', '[email protected]'),

('7', 'Mary', 'Abernathy', 'Finance', 'FA3', '6', '360-285-8420', '[email protected]'),

('8', 'Tom', 'Caruthers', 'Accounting', 'FA2', '6', '360-285-8430', '[email protected]'),

('9', 'Heather', 'Jones', 'Accounting', 'FA2', '6', '360-825-8440', '[email protected]'),

('10', 'Ken', 'Numoto', 'Sales and Marketing', 'SM3', '1', '360-285-8510', '[email protected]'),

('11', 'Linda', 'Granger', 'Sales and Marketing', 'SM3', '10', '360-285-8520', '[email protected]'),

('12', 'James', 'Nestor', 'InfoSystems', 'CIO', '1', '360-285-8610', '[email protected]'),

('13', 'Rick', 'Brown', 'InfoSystems', 'IS2', '12', NULL, '[email protected]'),

('14', 'Mike', 'Nguyen', 'Research and Development', 'CTO', '1', '360-285-8710', '[email protected]'),

('15', 'Jason', 'Sleeman', 'Research and Development', 'RD3', '14', '360-285-8720', '[email protected]'),

('16', 'Mary', 'Smith', 'Production', 'OPS3', '1', '360-825-8810', '[email protected]'),

('17', 'Tom', 'Jackson', 'Production', 'OPS2', '16', '360-825-8820', '[email protected]'),

('18', 'George', 'Jones', 'Production', 'CPS2', '17', '360-825-8830', '[email protected]'),

('19', 'Julia', 'Hayakawa', 'Production', 'CPS1', '17', NULL, '[email protected]'),

('20', 'Sam', 'Stewart', 'Production', 'OPS1', '17', NULL, '[email protected]')

INSERT Project(ProjectID, ProjectName, Department, MAxHours, StartDate, EndDate)

VALUES ('1000', '2017 Q3 Production Plan', 'Production', '100.00', '05/10/17', '2017-06-15'),

('1100', '2017 Q3 Marketing Plan', 'Sales and Marketing', '135.00', '05/10/17', '2017-06-15'),

('1200', '2017 Q3 Portfolio Analysis', 'Finance', '120.00', '07/05/17', '2017-07-25'),

('1300', '2017 Q3 Tax Preparation', 'Accounting', '145.00', '08/10/17', '2017-10-15'),

('1400', '2017 Q4 Production Plan', 'Production', '100.00', '08/10/17', '2017-09-15'),

('1500', '2017 Q4 Marketing Plan', 'Sales and Marketing', '135.00', '08/10/17', '2017-09-15'),

('1600', '2017 Q4 Portfolio Analysis', 'Finance', '140.00', '10/05/17', NULL)

INSERT Assignment(ProjectID, EmployeeNumber, HoursWorked)

VALUES('1000', '1', '30.00'),

('1000', '6', '50.00'),

('1000', '10', '50.00'),

('1000', '16', '75.00'),

('1000', '17', '75.00'),

('1100', '1', '30.00'),

('1100', '6', '75.00'),

('1100', '10', '55.00'),

('1100', '11', '55.00'),

('1200', '3', '20.00'),

('1200', '6', '40.00'),

('1200', '7', '45.00'),

('1200', '8', '45.00'),

('1300', '3', '25.00'),

('1300', '6', '40.00'),

('1300', '8', '50.00'),

('1300', '9', '50.00'),

('1400', '1', '30.00'),

('1400', '6', '50.00'),

('1400', '10', '50.00')

Answer 1

Short Summary:

• Error has been found and fixed.

**************Please upvote the answer and appreciate our time.************

Solution:

Erro:

Foreign key violation occurs in Insertion statement 12 and 13 because of department key violation.

In Department table, spelling mistake

('InfoSysems', 'BC-700-10', 'BLDG02-210', '360-285-8600');

In Employee table ,

('12', 'James', 'Nestor', 'InfoSystems', 'CIO', '1', '360-285-8610', '[email protected]'),
('13', 'Rick', 'Brown', 'InfoSystems', 'IS2', '12', NULL, '[email protected]')

Fix:

Either change the key name in department table as "InfoSystems" and run the employee table code you have.

Correct Solution:

• Just changed one line in Department table

CREATE TABLE DEPARTMENT (
DepartmentName Char(35) NOT NULL,
BudgetCode Char(30) NOT NULL,
OfficeNumber Char(15) Not Null,
DepartmentPhone Char(12) 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)
);

ALTER TABLE EMPLOYEE AUTO_INCREMENT=1;

CREATE TABLE PROJECT(
ProjectID Int NOT NULL AUTO_INCREMENT,
ProjectName Char(50) NOT NULL,
Department Char(35) NOT NULL,
MaxHours Numeric(8,2) NOT NULL DEFAULT 100,
StartDate Date NULL,
EndDate Date NULL,
CONSTRAINT PROJECT_PK PRIMARY KEY(ProjectID),
CONSTRAINT PROJ_DEPART_FK FOREIGN KEY(Department)
REFERENCES DEPARTMENT(DepartmentName)
ON UPDATE CASCADE
);

ALTER TABLE PROJECT AUTO_INCREMENT=1000;

CREATE TABLE ASSIGNMENT (
ProjectID Int NOT NULL,
EmployeeNumber Int NOT NULL,
HoursWorked Numeric(6,2) NULL,
CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID, EmployeeNumber),
CONSTRAINT ASSIGN_PROJ_FK FOREIGN KEY(ProjectID)
REFERENCES PROJECT(ProjectID)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT ASSIGN_EMP_FK FOREIGN KEY(EmployeeNumber)
REFERENCES EMPLOYEE(EmployeeNumber)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);

INSERT Department(DepartmentName, BudgetCode, OfficeNumber, DepartmentPhone)
VALUES('Administration', 'BC-100-10', 'BLDG01-201', '360-285-8100'),
('LEGAL', 'BC-200-10', 'BLDG01-220', '360-285-8200'),
('Human Resources', 'BC-300-10', 'BLDG01-230', '360-285-8300'),
('Finance', 'BC-400-10', 'BLDG01-110', '360-285-8400'),
('Accounting', 'BC-500-10', 'BLDG01-120', '360-285-8405'),
('Sales and Marketing','BC-600-10', 'BLDG01-250', '360-285-8500'),
('InfoSystems', 'BC-700-10', 'BLDG02-210', '360-285-8600'),
('Research and Development', 'BC-800-10', 'BLDG02-250', '360-285-8700'),
('Production', 'BC-900-10', 'BLDG02-110', '360-285-8800');

INSERT Employee(EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress)
VALUES('1', 'Mary', 'Jacobs', 'Administration', 'CEO', NULL, '360-285-8110', '[email protected]'),
('2', 'Rosalie', 'Jackson', 'Administration', 'AdminAsst', '1', '360-825-8120', '[email protected]'),
('3', 'Richard', 'Bandalone', 'Legal', 'Attorney', '1', '360-285-8210', '[email protected]'),
('4', 'George', 'Smith', 'Human Resources', 'HR3', '1', '360-285-8310', '[email protected]'),
('5','Alan', 'Adams', 'Human Resources', 'HR1', '4', '360-285-8320', '[email protected]'),
('6', 'Ken', 'Evans', 'Finance', 'CFO', '1', '360-285-8410', '[email protected]'),
('7', 'Mary', 'Abernathy', 'Finance', 'FA3', '6', '360-285-8420', '[email protected]'),
('8', 'Tom', 'Caruthers', 'Accounting', 'FA2', '6', '360-285-8430', '[email protected]'),
('9', 'Heather', 'Jones', 'Accounting', 'FA2', '6', '360-825-8440', '[email protected]'),
('10', 'Ken', 'Numoto', 'Sales and Marketing', 'SM3', '1', '360-285-8510', '[email protected]'),
('11', 'Linda', 'Granger', 'Sales and Marketing', 'SM3', '10', '360-285-8520', '[email protected]'),
('12', 'James', 'Nestor', 'InfoSystems', 'CIO', '1', '360-285-8610', '[email protected]'),
('13', 'Rick', 'Brown', 'InfoSystems', 'IS2', '12', NULL, '[email protected]'),
('14', 'Mike', 'Nguyen', 'Research and Development', 'CTO', '1', '360-285-8710', '[email protected]'),
('15', 'Jason', 'Sleeman', 'Research and Development', 'RD3', '14', '360-285-8720', '[email protected]'),
('16', 'Mary', 'Smith', 'Production', 'OPS3', '1', '360-825-8810', '[email protected]'),
('17', 'Tom', 'Jackson', 'Production', 'OPS2', '16', '360-825-8820', '[email protected]'),
('18', 'George', 'Jones', 'Production', 'CPS2', '17', '360-825-8830', '[email protected]'),
('19', 'Julia', 'Hayakawa', 'Production', 'CPS1', '17', NULL, '[email protected]'),
('20', 'Sam', 'Stewart', 'Production', 'OPS1', '17', NULL, '[email protected]')

INSERT Project(ProjectID, ProjectName, Department, MAxHours, StartDate, EndDate)
VALUES ('1000', '2017 Q3 Production Plan', 'Production', '100.00', '05/10/17', '2017-06-15'),
('1100', '2017 Q3 Marketing Plan', 'Sales and Marketing', '135.00', '05/10/17', '2017-06-15'),
('1200', '2017 Q3 Portfolio Analysis', 'Finance', '120.00', '07/05/17', '2017-07-25'),
('1300', '2017 Q3 Tax Preparation', 'Accounting', '145.00', '08/10/17', '2017-10-15'),
('1400', '2017 Q4 Production Plan', 'Production', '100.00', '08/10/17', '2017-09-15'),
('1500', '2017 Q4 Marketing Plan', 'Sales and Marketing', '135.00', '08/10/17', '2017-09-15'),
('1600', '2017 Q4 Portfolio Analysis', 'Finance', '140.00', '10/05/17', NULL);

INSERT Assignment(ProjectID, EmployeeNumber, HoursWorked)
VALUES('1000', '1', '30.00'),
('1000', '6', '50.00'),
('1000', '10', '50.00'),
('1000', '16', '75.00'),
('1000', '17', '75.00'),
('1100', '1', '30.00'),
('1100', '6', '75.00'),
('1100', '10', '55.00'),
('1100', '11', '55.00'),
('1200', '3', '20.00'),
('1200', '6', '40.00'),
('1200', '7', '45.00'),
('1200', '8', '45.00'),
('1300', '3', '25.00'),
('1300', '6', '40.00'),
('1300', '8', '50.00'),
('1300', '9', '50.00'),
('1400', '1', '30.00'),
('1400', '6', '50.00'),
('1400', '10', '50.00')

**************************************************************************************

Feel free to rate the answer and comment your questions, if you have any.

Please upvote the answer and appreciate our time.

Happy Studying!!!

**************************************************************************************