SQL Server – Check if Key Value is Referenced in Related Tables

foreign keysql server

I have one table in the database having ID as the primary key. Now this ID is referenced (as Foreign Key) in many other tables. How can I create a result with each row that whether it is referenced or not. As I want to prevent users from deleting items that have references. So the output should be:

ID   Title(Another Column in the table)   IsReferenced
--   ----------------------------------   ---------------------------------
1    Title 1                              True (or Count any one will work)
2    Title 2                              True
3    Title 3                              False
4    Title 4                              False

Best Answer

Given this data:

USE tempdb;
GO

CREATE TABLE dbo.Books
(
  BookID int PRIMARY KEY, 
  title varchar(32)
);
-- insert 3 rows
INSERT dbo.Books(BookID, title) VALUES(1,'no relations'),
    (2,'one relation'),(3,'all relations');

CREATE TABLE dbo.OverdueBooks
(
  BookID int NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
-- insert 1 row
INSERT dbo.OverdueBooks(BookID) VALUES(2);

CREATE TABLE dbo.OneStarBooks
(
  BookID int NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookID)
);
-- insert 2 rows
INSERT dbo.OneStarBooks(BookID) VALUES(2),(3);

You can avoid knowing all of the tables and columns that reference the parent by building dynamic SQL from the metadata about the foreign keys:

-- all you need to identify is the table / column that is the parent:
DECLARE @parent nvarchar(800) = N'dbo.Books.BookID';



DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N' 
UNION ALL SELECT ' + QUOTENAME(c.name) + N' AS id FROM '
 + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' WHERE '
 + QUOTENAME(c.name) + N' = ' + PARSENAME(@parent,2) + N'.' + PARSENAME(@parent,1)
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns AS c
ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
INNER JOIN sys.objects AS o
ON c.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE fk.referenced_object_id = OBJECT_ID(PARSENAME(@parent,3)+N'.'+PARSENAME(@parent,2));

SET @sql = N'SELECT Books.BookID, Books.Title, 
  IsReferenced = MIN(CASE WHEN x.id IS NOT NULL THEN ''True'' ELSE ''False'' END)
  FROM dbo.Books
  OUTER APPLY 
  (
  ' + STUFF(@sql,1,CHARINDEX(N'SELECT',@sql)-1,N'') + N'
  ) AS x(id)
GROUP BY Books.BookID, Books.Title;';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

When you're happy with the PRINT output (if you have tons of foreign keys, it may get truncated; see this tip for ideas), uncomment the EXEC. My print output looked like this:

SELECT Books.BookID, Books.Title, 
  IsReferenced = MIN(CASE WHEN x.id IS NOT NULL THEN 'True' ELSE 'False' END)
  FROM dbo.Books
  OUTER APPLY 
  (
  SELECT [BookID] AS id FROM [dbo].[OverdueBooks] WHERE [BookID] = Books.BookID 
UNION ALL SELECT [BookID] AS id FROM [dbo].[OneStarBooks] WHERE [BookID] = Books.BookID
  ) AS x(id)
GROUP BY Books.BookID, Books.Title;

And when I ran that I got the expected:

BookID  Title          IsReferenced
------  -------------  ------------
1       no relations   False
2       one relation   True
3       all relations  True

Don't forget to clean up tempdb:

DROP TABLE dbo.OneStarBooks, dbo.OverdueBooks, dbo.Books;
Related Question