--DROP TABLE Departments
--DROP TABLE Employees

CREATE TABLE Employees
(
ID int PRIMARY KEY,
Name VARCHAR(20)
)


INSERT INTO Employees VALUES (0, '%NO EMPLOYEE%')
INSERT INTO Employees VALUES (102, 'Joe')
INSERT INTO Employees VALUES (103, 'Jane')
INSERT INTO Employees VALUES (104, 'Joan')

SELECT * FROM Employees

--DROP TABLE Departments

CREATE TABLE Departments
(
ID int PRIMARY KEY,
Manager int DEFAULT 0
FOREIGN KEY REFERENCES Employees(ID)
ON DELETE SET NULL ON UPDATE SET DEFAULT
)




DEFAULT 1001 CONSTRAINT FK_emp FOREIGN KEY REFERENCES Employees(ID)
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT,
BackupManager int DEFAULT 101 CONSTRAINT FK_bkp FOREIGN KEY REFERENCES Employees(ID)
---ON DELETE SET DEFAULT
)


INSERT INTO Departments VALUES (1999, 102)
SELECT * FROM Departments

DELETE Employees where ID = 102
UPDATE Employees SET ID=1000 where ID = 102
U

BEGIN TRAN
UPDATE Departments SET Manager=103 WHERE ID=1999
DELETE Employees where ID = 102
COMMIT TRAN

SELECT * From departments


UPDATE Employees SET ID = 110 where ID=102
SELECT * FROM Departments
DELETE Employees WHERE ID = 0
SELECT * FROM Departments

--DROP TABLE Departments2
--DROP TABLE Employees2

CREATE TABLE Employees2
(
ID int PRIMARY KEY,
Name VARCHAR(20)
)

CREATE TABLE Departments2
(
ID int PRIMARY KEY,
Manager int DEFAULT 0 
FOREIGN KEY REFERENCES Employees2(ID)
ON DELETE SET DEFAULT
)


INSERT INTO Employees2 VALUES (0, '%NO EMPLOYEE%')
INSERT INTO Employees2 VALUES (102, 'Joe')
INSERT INTO Employees2 VALUES (103, 'Jane')
INSERT INTO Employees2 VALUES (104, 'Joan')

INSERT INTO Departments2 VALUES (1999, 102)

DELETE Employees2 WHERE ID = 102

SELECT * FROM Departments2

TRUNCATE TABLE Departments2
INSERT INTO Departments2 VALUES (1999, NULL)

