Managing untrusted foreign keys

SQLShack

SQL Server training Español

Managing untrusted foreign keys

March 17, 2016 by Jefferson Elias

Introduction

Intended audience This article is intended for application developers and database administrators who plan to develop, deploy, and/or assess solutions for Microsoft SQL Server on a Microsoft Windows platform. Typographical Conventions Convention Meaning Stylized Consolas Font Used for blocks of code, commands and script examples.
Text should be interpreted exactly as presented Consolas Font Used for inline code, commands or examples.
Text should be interpreted exactly as presented Italic texts set in angle brackets denote a variable requiring substitution for a real value Italic font Used to denote the title of a book, article, or other publication Note Additional information or caveats Overview The subject of this article is untrusted foreign keys. Those three words together have a meaning but each word has to be well understood before that. That’s the reason why this article will start with a definitions section where we will define what is a key, what is a foreign key, and how a foreign key can be “untrusted”. Then, we will cover the way to discover and repair untrusted foreign keys for a given database. Finally, we will demonstrate in detail how foreign keys influence the execution of a query when it does not exist, when it exists and is “trusted” and when it exists and is “untrusted”.

Definitions

Before going any further, let’s set the base and answer the following questions: « What is a key? », « What is a primary key? » and finally « What is a foreign key? », which is the topic of this article. Then, we can talk about what the difference between a trusted and an untrusted foreign key. What is a key for a relational table? A key is basically an identifier, something that can uniquely identify a record in a relational table. Keys may consist of a single attribute or multiple attributes in combination. Based on the design of a table, there can be more than just one key which identify a record. We will refer to them as candidate keys. Example: Let’s have a look at the following table. It represents a list of students with 4 columns: a random numeric identifier and the firstname, lastname and sex of the student. StudentID FirstName LastName Sex 5345664 Adam Kent Male 8795165 Jefferson Elias Male There are two candidate keys here: the StudentID, and the combination of FirstName and LastName columns. Let’s add a table with the courses that are given in a particular school. The table will contain a unique identifier for the course, a title and a description. CourseId Title CourseDescription 7897 Networks An introductive course on network topologies and standards 8975 Numerical Analysis Basics on numerical analysis. There are, also, two candidate keys here: the CourseId column alone and the Title column alone. What is a primary key for a relational table? The primary key of a relational table is a key that is considered as the most appropriate one among all the acceptable keys for this table. As a key, it can either be a normal attribute that is guaranteed to be unique such as a unique random alpha-numeric identifier or it can be generated by the DBMS. In our example, the most appropriate key to identify a given student record is the StudentID column. What is a foreign key in a relational table? A foreign key is a basically a reference to another table in a DBMS. It comprises all the key columns composing the primary key of that « foreign » or « parent » table. One who defines a foreign key creates at the same time a referential constraint that checks data provided as a foreign key reference an existing primary key value in the parent table. This constraint must be validated by the DBMS before accepting any kind of modification on the table. So, we can say that a foreign key ensures data integrity. If we carry on with our example, let’s say we have table called StudentEnrollments which keeps track of the enrollments a student has made for current year and his final mark. StudentId CourseId FinalMark 5345664 7897 16 8795165 8975 9 5345664 7897 18 Here we have two foreign keys: StudentID column refers to the primary key in the Students table presented previously ; CourseId column refers to the primary key in the StudentEnrollments table also presented previously. As a final question for readers, what could possibly be the primary key we can use for this particular table? Answer: the combination of both foreign keys, i.e. (StudentId,CourseId). Note
Usually, foreign key references the primary key in a different table. It’s what have been shown in the examples. It’s also good to use it inside the same table. Such a design can be used to create hierarchical ordering in a table. You will find below an example of this: a table called department which has a unique identifier and can be member of another department. DeptId Name HeadOfDept_id ParentDeptId 10 Finance 2 (null) 20 Accounting 23 10 Here the Finance department has no parent department but has a child department which is the Accounting. Disabling a foreign key constraint or the way to untrust a foreign key SQL Server allows you to temporarily disable any CHECK or FOREIGN KEY constraint. Common use cases for this particular features are in the area of copying or importing data to a table faster. The process will work for sure, but one who does this operation must ensure that data he’s copying won’t violate those constraints before activate it again. Here is the syntax to be used to disable then enable a constraint: 1234567 ALTER TABLE . NOCHECK CONSTRAINT ;ALTER TABLE . WITH { CHECK NOCHECK } CHECK CONSTRAINT ; Specifying WITH CHECK in a statement tells to SQL Server the user wants it to validate the constraint against every single row in the table, then, if successful, enable it. In contrast, specifying WITH NOCHECK, which is the default for an existing constraint, means that the constraint is enabled but no validation has been made on it. Even if this mode is faster to run, it can lead to severe side effects on performance: SQL Server doesn’t trust the constraint as it has not validated it. We refer to such a foreign key as an « untrusted foreign key ». As a consequence, the query optimizer won’t use the constraint to do his job… Human error can occur: one can forget to re-enable the constraint. The proof of this will be given in the demo section.

Detect untrusted foreign keys and take the appropriate action

There is a simple way to detect whether a database contains one or more untrusted foreign keys. It’s simply by querying the sys.foreign_keys view in that database and check the is_not_trusted column. If this column is set to 1, it means the constraint is untrusted. Here is a possible version for the query: 123456789 SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keynameFROM sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_idWHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0 This method is simple but not practical because you must do it against every single database on a SQL Server instance. Instead, we developed a stored procedure that will return a dataset with all the untrusted foreign keys for every database plus the code to solve the issue. The procedure is called [Administration].[GetUntrustedForeignKeys]. It creates a global temporary table you can then reuse in any other procedure. This table will contain the information described above. You will find below its interface. You can specify a database name. If you don’t, it will run against every accessible database on the instance. There are also some parameters that influence the behavior of the procedure. 12345678 PROCEDURE [Administration].[GetUntrustedForeignKeys] ( @DbName VARCHAR(256) = NULL, @_NoTempTableDropBefore BIT = 0, @_NoTempTableDropAtTheEnd BIT = 0, @_NoSelectOnTempTable BIT = 0) The table that is sent back is of the current form: 1234567 DbName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256), ForeignKeyName VARCHAR(256), DDL2Resolve VARCHAR(MAX) – computed column In addition, we defined another stored procedure built on the previous one that effectively runs the code defined in the DDL2Resolve column. It’s called [Administration].[RunCheckUntrustedForeignKeys].

Foreign key and performance Demo

Let’s begin this section by creating a database for testing and some tables with primary keys. The tables will be the ones shown in the examples: Students, StudentEnrollments and Courses. Database Creation Statements. 1234567891011121314151617181920212223242526272829303132 CREATE DATABASE [TestFK] CONTAINMENT = NONE ON PRIMARY ( NAME = N'TestFK', FILENAME = N'E:\DBASQL2K12T\TestFK.mdf' , SIZE = 131072KB , MAXSIZE = 16777216KB , FILEGROWTH = 262144KB )LOG ON ( NAME = N'TestFK_log', FILENAME = N'L:\DBASQL2K12T\TestFK_log.ldf' , SIZE = 262144KB , MAXSIZE = 16777216KB , FILEGROWTH = 262144KB );GO USE [TestFK]GO IF NOT EXISTS ( SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') BEGIN ALTER DATABASE [TestFK] MODIFY FILEGROUP [PRIMARY] DEFAULT;END;GO
Creation of the Students table. 1234567891011121314 use TestFK; GO IF(OBJECT_ID('[dbo].[Students]') IS NULL)BEGIN CREATE TABLE [dbo].[Students] ( StudentID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(128) NOT NULL, LastName VARCHAR(128) NOT NULL, Sex VARCHAR(6) NOT NULL );END;
Creation of the Courses table. 12345678910111213 use TestFK; GO IF(OBJECT_ID('[dbo].[Courses]') IS NULL)BEGIN CREATE TABLE [dbo].[Courses] ( CourseID INT IDENTITY(1,1) PRIMARY KEY, Title VARCHAR(128) NOT NULL, CourseDescription VARCHAR(MAX) );END;
Creation of the StudentEnrollments table. 123456789101112131415161718192021222324252627 use TestFK; GO IF(OBJECT_ID('[dbo].[StudentEnrollments]') IS NULL)BEGIN CREATE TABLE [dbo].[StudentEnrollments] ( StudentID INT NOT NULL, CourseID INT NOT NULL, FinalMark INT NULL );END;IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[StudentEnrollments]') AND name = N'PK_StudentEnrollments')BEGIN ALTER TABLE [dbo].[StudentEnrollments] ADD CONSTRAINT [PK_StudentEnrollments] PRIMARY KEY CLUSTERED ( [StudentID] ASC, [CourseID] ASC );END;GO
As you may have noticed, we haven’t created the foreign key constraints yet. Let’s first get an overview of the way SQL Server handles a query without this foreign key. You will find below a query that uses the StudentEnrollments table with the other two tables to get back a view of the name and firstname of a student following a course. 12345678910 set statistics io onset statistics time onselect s.FirstName,s.LastName from [dbo].[StudentEnrollments] seinner join [dbo].[Students] s on se.StudentID = s.StudentIDinner join [dbo].[Courses] c on se.CourseID = c.CourseID ; Here is its actual execution plan. Here are statistics on I/O and time: Let’s now create the foreign key references and see if there is a difference. Create the Foreign Key constraint to the Courses table. 1234567891011121314151617181920 USE [TestFK]GO IF NOT EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentEnrollments_Courses]') AND parent_object_id = OBJECT_ID(N'[dbo].[StudentEnrollments]'))BEGIN ALTER TABLE [dbo].[StudentEnrollments] ADD CONSTRAINT [FK_StudentEnrollments_Courses] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Courses] ([CourseID]) ;END;GO
Create the Foreign Key constraint to the Students table. 1234567891011121314151617181920 USE [TestFK]GO IF NOT EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentEnrollments_Students]') AND parent_object_id = OBJECT_ID(N'[dbo].[StudentEnrollments]'))BEGIN ALTER TABLE [dbo].[StudentEnrollments] ADD CONSTRAINT [FK_StudentEnrollments_Students] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Students] ([StudentID]) ;END;GO The newer foreign keys change the execution plan of the query we used for test as you will see below. But first, the previous query: 12345678910 set statistics io onset statistics time onselect s.FirstName,s.LastName from [dbo].[StudentEnrollments] seinner join [dbo].[Students] s on se.StudentID = s.StudentIDinner join [dbo].[Courses] c on se.CourseID = c.CourseID ; And the resulting actual plan: There is no access to the [dbo].[Courses] table introduced by the inner join due to foreign key addition. As a conclusion, foreign keys may reduce I/O, CPU and time for SELECT queries. Now, we will deactivate the foreign keys and insert data into tables then reactivate the foreign key. We will see whether this implies a different behavior or not and check that the foreign keys are untrusted. 123456 Alter table [dbo].[StudentEnrollments] nocheck constraint [FK_StudentEnrollments_Students];Alter table [dbo].[StudentEnrollments] nocheck constraint [FK_StudentEnrollments_Courses]; Are those foreign keys untrusted (using the query shown in Section 4). The query: 123456789 SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keynameFROM sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_idWHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0 Its result: Conclusion: No, the foreign keys are not untrusted at the moment. Let’s populate the tables. 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647 SET NOCOUNT ON ;DECLARE @RowCount INT;DECLARE @CurrentSex VARCHAR(6);DECLARE @RowIdStudent INT;SET @RowCount = 0;BEGIN TRANSACTION;WHILE @RowCount < 21BEGIN if(@RowCount %2 = 0) BEGIN SET @CurrentSex = 'Male'; END; ELSE BEGIN SET @CurrentSex = 'Female'; END; insert into [dbo].[Students](FirstName,LastName,Sex) values ( REPLICATE('a',@RowCount%6+1), REPLICATE('b',@RowCount%6+1), @CurrentSex ); If(@RowCount % 10 = 0) BEGIN Insert into [dbo].[Courses] ( Title,CourseDescription ) values( 'Course : ' + CONVERT(VARCHAR(20),@RowCount), 'Course from ' + CONVERT(VARCHAR(20),@RowCount) ); END; SET @RowCount = @RowCount + 1;END;COMMIT; insert into StudentEnrollments ( StudentID,CourseID)select StudentId, 1from Students where StudentID % 7 = 0union all select StudentId, 2from Students where StudentID % 7 = 1; Are the foreign keys untrusted now? No, not yet as shown by the result of our simple detection query: So, let’s re-enable foreign keys. 123456 Alter table [dbo].[StudentEnrollments] check constraint [FK_StudentEnrollments_Students];Alter table [dbo].[StudentEnrollments] check constraint [FK_StudentEnrollments_Courses]; Are the foreign keys untrusted now? Definitely, yes as shown by the result of our simple detection query: Did it change something in the query plan for our test query? Let’s run it again and check its actual execution plan and statistics. The actual query plan is the following one: While the statistics are: Now let’s run our procedure [Administration].[GetUntrustedForeignKeys]. Here is the T-SQL code to run the procedure for the test database alone: 1234 exec SAIDBA.[Administration].[GetUntrustedForeignKeys] @DbName = 'TestFK'; Here is the result given by the procedure: The DDL2Resolve value for the first untrusted foreign is: 123456 USE [TestFK];PRINT 'TestFK.dbo.StudentEnrollments(FK_StudentEnrollments_Courses)';ALTER TABLE [dbo].[StudentEnrollments] WITH CHECK CHECK CONSTRAINT [FK_StudentEnrollments_Courses]; Let’s run both commands as the number of untrusted foreign keys is limited. Did we finally solve the problem? Let’s check that there is no untrusted foreign key anymore. The simple audit query: 123456789 SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keynameFROM sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_idWHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0 Its result: The test query: 12345678910 set statistics io onset statistics time onselect s.FirstName,s.LastName from [dbo].[StudentEnrollments] seinner join [dbo].[Students] s on se.StudentID = s.StudentIDinner join [dbo].[Courses] c on se.CourseID = c.CourseID ; Its actual plan: Its execution statistics: As the demo comes to its end, there is a final action to do: cleanup. Database Cleanup Statements. 12345678910111213141516171819202122232425262728293031323334 Use [TestFK];GO/*TRUNCATE TABLE Courses;TRUNCATE TABLE Students;TRUNCATE TABLE StudentEnrollments;*/IF(OBJECT_ID('[dbo].[StudentEnrollments]') IS NOT NULL)BEGIN EXEC sp_executesql N'DROP TABLE [dbo].[StudentEnrollments]';END;GOIF(OBJECT_ID('[dbo].[Students]') IS NOT NULL)BEGIN EXEC sp_executesql N'DROP TABLE [dbo].[Students]';END;GO IF(OBJECT_ID('[dbo].[Courses]') IS NOT NULL)BEGIN EXEC sp_executesql N'DROP TABLE [dbo].[Courses]';END;GO USE [master];GO IF(DB_ID('TestFK') IS NOT NULL)BEGIN EXEC sp_executesql N'DROP DATABASE [TestFK]';END;GO Author Recent Posts Jefferson EliasLiving in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege.

I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.

I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias Latest posts by Jefferson Elias (see all) How to perform a performance test against a SQL Server instance - September 14, 2018 Concurrency problems – theory and experimentation in SQL Server - July 24, 2018 How to link two SQL Server instances with Kerberos - July 5, 2018

Related posts

SQL Foreign key DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key How to Index Foreign Key Columns in SQL Server Foreign key issues while applying a snapshot in SQL Server merge replication Recursive CTEs and Foreign Key References in SQL Server 5,717 Views

Follow us

Popular

SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices

Trending

SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples

Solutions

Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server

Categories and tips

►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ▼Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy