SQL Server constraint on delete

Question

I created this table:

CREATE TABLE OfficialEmployee
(
    EID Integer not null foreign key references Employee(EID),
    StartWorkingDate date not null ,
    Degree char(20) not null,
    Department char(50) not null,
    DID Integer not null foreign key references Department(DID)
);

which references the table Employee by the DID:

CREATE TABLE Employee
(   
    EID Integer not null PRIMARY KEY,
    FirstName char(30) not null,
    LastName char(30) not null,
    BirthDate date not null,
    CellPhoneNumber Integer not null,
    City char(30) not null,
    StreetName char(30) not null,
    Number Integer not null,
    Door Integer not null
);

CREATE TABLE Department 
(
    DID Integer not null PRIMARY KEY,
    Name char(30) not null,
    Description char(200) not null,
    Manage Integer not null FOREIGN KEY REFERENCES OfficialEmployee(EID)
);

and I want to make a constraint that when OfficialEmployee is deleted, the record of his in Employee will be deleted too only if he is not a manager (in the Department table) else it will (using cascade).

How can I do that?

(I'm using SQL Server)

You haven't said which SQL you are using, but you could have a look at: [How to delete rows in tables that contain foreign keys to other tables](https://stackoverflow.com/questions/3656099/how-to-delete-rows-in-tables-that-contain-foreign-keys-to-other-tables) — lurker, Nov 24 '18 at 22:36
But in that post i,t shows how to delete any time a record is deleted,I want to delete "only if" something — user111434, Nov 24 '18 at 22:42
What you're trying to accomplish isn't going to work with constraints, but is possible with table triggers. — Eric Brandt, Nov 24 '18 at 23:15
Side note: I'd **strongly** recommend *not* to use `char(30)` etc. datatypes - `char(n)` will always pad the column data with spaces, to the defined length - so you always have 30 (or 50, or 200) characters in a column like this - even if your value is only 3 characters long. You should use **varchar(n)** instead — marc_s, Nov 25 '18 at 07:58

Tina Sebastian · Answer 1 · 2018-11-25T15:26:38.707

0

Assuming that the description you want to avoid is 'Manager', here is a simple trigger.

CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE
AS
    DELETE FROM dbo.Employee
    WHERE EID IN (SELECT deleted.EID FROM deleted) 
      AND NOT EXISTS (SELECT * FROM Department 
                      WHERE Manage = deleted.EID AND Description = 'Manager')

edited Nov 25 '18 at 15:26

answered Nov 25 '18 at 02:59

Tina Sebastian

34
2

score 0 · Answer 2 · answered Nov 25 '18 at 13:17

In SQL Server, you have to remember that deleted (and inserted can have more than one record.

So, do this using JOIN:

CREATE TRIGGER deleteEmployeeTrigger
ON dbo.OfficialEmployee
FOR DELETE AS
BEGIN
    DELETE e
        FROM dbo.Employee e JOIN
             dbo.deleted dd
             ON e.EID = dd.EID LEFT JOIN
             dbo.department d
             ON d.manage = dd.EID
    WHERE d.manage IS NULL;
END;

SQL Server constraint on delete

2 Answers2