How do I create a constraint in a junction table that disallows null values from parent tables?

user_15091925 0
Viewed 133

I've edited my question.

I need to stop an insert or update in a junction table where a value in a column in parent table is NULL. The column in the other table is not unique, i.e. not a primary key.

These are my tables:

CREATE TABLE EMPLOYEE (
eID int PRIMARY KEY,
fName varchar(50) NOT NULL,
lName varchar(50) NOT NULL,
Job varchar(50) NOT NULL,
Manager int NULL,
Hired date NOT NULL,
);

CREATE TABLE "SHIFT" (
sID int PRIMARY KEY,
Day text NOT NULL,
startTime time NOT NULL,
endTime time NOT NULL,
);

CREATE TABLE EMPLOYEE_SHIFT(
ESID int PRIMARY KEY NOT NULL,
EID int NOT NULL,
SID int NOT NULL,
FOREIGN KEY (EID) REFERENCES EMPLOYEE(EID), 
FOREIGN KEY (SID) REFERENCES SHIFT(SID));

Sample input data

INSERT INTO EMPLOYEE_SHIFT VALUES (NEXT VALUE FOR SQ1, 101, 1)
INSERT INTO EMPLOYEE_SHIFT VALUES (NEXT VALUE FOR SQ1, 102, 2)

INSERT INTO EMPLOYEE VALUES (101,'Adam','Smith','Manager','102','1998-04- 
12'); 
INSERT INTO EMPLOYEE VALUES  (102,'Marry','Jane','CEO',NULL,'1997-01-18');  

INSERT INTO "SHIFT" VALUES (1,'Monday','09:00:00','17:00:00'); 
INSERT INTO "SHIFT" VALUES (2,'Tuesday','09:00','17:00');

Trigger

CREATE TRIGGER TRIGGER01
ON EMPLOYEE_SHIFT
AFTER INSERT
AS
IF NOT EXISTS (select * from EMPLOYEE e
inner join SHIFT s
ON e.eid = s.sid
where MANAGER IS NOT NULL)
BEGIN
RAISERROR('THIS EMPLOYEE DOESN'T HAVE A MANAGER', 10, 1)
ROLLBACK
RETURN
END

The trigger is working, but not by checking if the Manager column is NULL for that unique row. How can I get it to check for each inserted row uniquely?

sql-server sql
1 Answers

The answer to your trigger problem is the perennial issue we see with triggers: no join on inserted and deleted pseudo-tables.

Furthermore, when you rollback in a trigger, you get an error "Transaction ended in the trigger", which isn't very user-friendly. So instead use THROW and rely on the server rolling back the implicit transaction:

CREATE TRIGGER Trg_Employee_Shift_INS  -- give proper trigger names
ON EMPLOYEE_SHIFT
AFTER INSERT, UPDATE    -- we can do this for updates too
AS

SET NOCOUNT, XACT_ABORT ON;  -- forces a rollback

IF NOT EXISTS (select 1
    from EMPLOYEE e
    inner join inserted i ON i.eid = e.EID 
    where e.MANAGER IS NOT NULL)
BEGIN
    THROW 50000, 'THIS EMPLOYEE DOESN'T HAVE A MANAGER', 0;
END;

You are probably going to want a trigger on the Employee table too.

Instead of all this, we can use a trick involving Indexed Views, that I picked up from spaghettidba.

I have written a fuller explanation of how it works in this post, but I will show you how to use it in your case.

Let's start by creating a dummy table, with exactly two rows in it:

CREATE TABLE dbo.DummyTwoRows (x bit NOT NULL PRIMARY KEY);
GO
INSERT dbo.DummyTwoRows VALUES (0),(1);

Now, we create a view that selects all rows that fail the condition we would like to enforce. We cross-join it with the above table, to get two rows for every failing row:

CREATE /* OR ALTER */ VIEW dbo.vwShiftEmployeesNoManagers
WITH SCHEMABINDING    -- must be schema-bound, so can't change underlying columns
AS
SELECT 1 AS DummyOne
FROM dbo.Employee e    -- must use two-part names
JOIN dbo.Employee_Shift es ON es.eID = e.eID
CROSS JOIN dbo.DummyTwoRows
WHERE e.Manager IS NULL;

Now this is the magic:

When we create a clustered index on the view, SQL Server will maintain it. So every row inserted or updated in the base tables will pass through the above join, so that the view can be materialized on disk.

Since every row gets doubled via the cross join, a unique constraint will fail if any row passes the WHERE filter.

So we create an index:

CREATE UNIQUE CLUSTERED INDEX CX_vwShiftEmployeesNoManagers
    ON dbo.vwShiftEmployeesNoManagers (DummyOne);

Note that since no rows can exist in the view, it will take up no space on disk. If your indexing is right, it should be cheap to maintain also.

Incidentally, your join table should probably not have it's own primary key. Instead, make up the primary key from the two foreign keys.

user_148689970
Related