USing SQL and corresponding to the table below; give the statements to perform t

Question

USing SQL and corresponding to the table below; give the statements to perform the following:

1. When deleting a record of an employee who is a manager, the managerID field of the department she/he works for should be set to null.

2. When deleting a record of a department, the deptName field of all employees who work in that department should be set to null.

Please respond, cannot find help anywhere.

Departments Name managerlD RD SALES Employees ID 123 Name John Robert Jenny Christ Bill Susan Salary 56000 37500 46000 39000 50000 67500 deptName RD HR HR SALES RD RD ?? 101

Explanation / Answer

Please Use ON DELETE SET NULL and create the tables as below,

create table Departments(

    Name varchar(50) NOT NULL,

    managerID int,

    PRIMARY KEY (Name),

    CONSTRAINT fk_managerID
    FOREIGN KEY (managerID)
    REFERENCES Employees(ID)
    ON DELETE SET NULL);

create table Employees(

    ID int NOT NULL,

    Name varchar(50),

    Salary int,

    deptName varchar(50),

    PRIMARY KEY (ID),

    CONSTRAINT fk_deptName
    FOREIGN KEY (deptName)
    REFERENCES Departments(Name)
    ON DELETE SET NULL);

or Alter the existing table using the following commands,

ALTER TABLE Departments ADD

CONSTRAINT fk_managerID FOREIGN KEY (managerID)

REFERENCES Employees(ID) ON DELETE SET NULL;

ALTER TABLE Employees ADD

CONSTRAINT fk_deptName FOREIGN KEY (deptName)

REFERENCES Departments(Name) ON DELETE SET NULL;

You will be get your expected behaviour.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.