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

Question

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?

Why is the SHIFT table in quotes in the DDL? Is that a typo? — SteveC, Jan 27 '21 at 15:02
The manager column is defined as `NOT NULL`, so your question is unclear. — Gordon Linoff, Jan 27 '21 at 15:05
@steveC I recall it SQL Studio threw a syntax error, so I inserted the quotes to get around it. I will review entire DLL after. — Zenith2k3, Jan 27 '21 at 15:06
@GordonLinoff because, I have rows inserted into EMPLOYEE table where the values are NULL. such as EID 102, has no Manager value. So I want to stop being able to insert (assign a shift) to an employee by using their EID, when they have no manager value, etc... An employee assigned a shift, must have a Manager value. — Zenith2k3, Jan 27 '21 at 15:09
`Day varchar(20) NOT NULL,` this smells like danger but only you know what you intend to store in this column. Your trigger syntax is not valid tsql - which suggests you are using a different database engine since you don't mention any error creating it. And no - you should start using kludges to avoid other problems. Designing databases is a very different skill. Understand your errors first along with your data model. Don't just throw more code or changes into it just to avoid errors. — SMor, Jan 27 '21 at 15:11
Does this answer your question? [Adding constraints that check a separate (linked) table for a value](https://stackoverflow.com/questions/65825778/adding-constraints-that-check-a-separate-linked-table-for-a-value) Specifically my answer https://stackoverflow.com/a/65827550/14868997 re **Indexed View** — Charlieface, Jan 27 '21 at 15:39
@Charlieface I couldn't bring that solution to work for me, however I've edited my question, with new attempt. — Zenith2k3, Feb 02 '21 at 17:05

score 1 · Accepted Answer · answered Feb 02 '21 at 21:35

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.

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

1 Answers1