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.



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!



Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook