Hi Friends,
Today we are going to explore how operator assert works with foreign keys. I recommend you to read yesterday’s post Assert operator SQL Server Part1 first in case you are reading today’s post ahead of it.
Let’s look at an example where this operator is used to validate a foreign key.
--Step1
ALTER TABLE Table1 ADD ID_Selected INT
GO
--Step2
IF OBJECT_ID('Table2') IS NOT NULL
DROP TABLE Table2
GO
CREATE TABLE Table2(ID Integer PRIMARY KEY, Selected CHAR(1))
GO
--Step3
INSERT INTO Table2(ID, Selected) VALUES(1, 'Y')
INSERT INTO Table2(ID, Selected) VALUES(2, 'N')
INSERT INTO Table2(ID, Selected) VALUES(3, 'Q')
GO
--Step4
ALTER TABLE Table1 ADD CONSTRAINT FK_Table2 FOREIGN KEY (ID_Selected) REFERENCES Table2(ID)
GO
--Step5
INSERT INTO Table1(ID, ID_Selected, Selected) VALUES(1, 4, 'X')
Assert operation appears twice in text plan validating check constraint, looking the right to left in the graphical plan and down to up in the text plan. If the exit value is 0 then keep running the query, but if NULL is returned shows an exception.
Next assert is validating joins between Table1 and Table2. We can see, Expr1008 is NULL and to understand what Expr1008 is, look at the Probe Value highlighted in blue which is the result of the join.
If the value passed to the INSERT at the column ID_Selected exists in the table Table2, then that probe will return the join value; else it will return NULL. Assert is checking the value of the search at the Table2; if the value that is passed to the INSERT is not found then Assert will throw an exception.
Stay tuned, I’ll be back tomorrow with more on assert operator.
Happy learning!
Regards,
Kanchan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
