MySQL Assume the following three are true. (1) These two tables already exist in
ID: 3733407 • Letter: M
Question
MySQL
Assume the following three are true.
(1) These two tables already exist in a database with data shown above.
(2) EmpID and Dname are the primary key of Employee and Department, respectively.
(3) These two tables are not related, that is, there is no foreign key constraint defined. Because lacking of foreign key constraint, an employee like E012 can have a dept value 'HR', which does not exist in Department. If the constraint is defined, every Dept value in Employee must refer to one of Dname values in Department.
If we want to keep all data without modifying or removing them out of these two tables, but we no longer allow the Dept value of an employee to refer to a non-existing Dname value of Department, we need to add a foreign key constraint in one of these two tables to implement their relationship.
Use ALTER TABLE statements to implement the above foreign key constraint. Pay attention to (1) determine the right table to contain the constraint, and (2) choose either 'WITH CHECK' or ' WITH NOCHECK' to include in the statement. Selection of this option is critical because of the underscored requirement described in the paragraph above.
Employee Department EmpID Dept Gender Dname Office R&D; K-71 Sales M-22 Sales F E012 HR E354Explanation / Answer
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT) REFERENCES DEPARTMENT(DNAME);
EXPLANATION:- we can alter the table to add a foreign key constraint by using ADD FOREIGN KEY CLAUSE with ALTER TABLE CLAUSE
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.