Assert operator SQL Server – Part2

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')

AssertFKGraphicalPlan

AssertFKTextPlan

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *