1

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
1

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;
| improve this answer | |
  • Its working perfectly fine. I have replaced the values with my table name. But the output is not complete. Its breaking after some Union All, it ends like this: ______________________________________ UNION ALL SELECT [WOADID] AS id FROM [dbo].[tblFRToMiscellaneousRestorationsTemp] WHERE [WOADID] = tblJobToWorkOrder.ID UNION ALL SELECT [NSRWOAD] AS id FROM [dbo].[tblFRToMiscellaneousRestorationsTemp] WHERE [NSRWOAD] = tblJobToWorkOrder.ID UNION ALL SELECT [JobToWorkOrderID] AS id FROM [dbo].[tblFRToNeuCoEmployee] WHERE [JobToWorkOrderID] = tblJobToWorkOrder.ID UNION ALL SELECT [J – Rishi May 8 '19 at 13:21
  • The column is referenced in 60 tables. – Rishi May 8 '19 at 13:26
  • If you are using PRINT to check it, yes, it will break due to limitations of PRINT, but the query will still run correctly when executed. To check that the end of the query is intact, PRINT RIGHT(@sql, 255); – Aaron Bertrand May 8 '19 at 15:57
  • Thanks.... Perfect... – Rishi May 9 '19 at 8:34
0

I have one table in the database having ID as the primary key.

OK

Now this ID is referenced (as Foreign Key) in many other tables.

Good.

... i want to disable those items from deleting.

As long as you haven't specified "ON DELETE CASCADE" on those Foreign Key constraints, there is nothing for you to do - the row cannot be deleted while any rows exist that reference it.

How can i create a result with each row that whether it is referenced or not.

Why would you need to? Do you want End Users to be deleting things in this fashion?

I would have thought this would be an "Administrative" function, for a limited, trainable cohort of people, who can be told that they might get an error if they try to delete something that can't be.

If you really must have this, consider the most common child table and count the related rows in that table to determine the "deletability" of the item.

| improve this answer | |
0

There are different possibilities to achieve this. Here are two of them:

First create sample data:

-- Structure 
DECLARE @Books TABLE (Id INT, Title VARCHAR(20));
DECLARE @Reference1 TABLE (BookId INT);
DECLARE @Reference2 TABLE (BookId INT);
DECLARE @Reference3 TABLE (BookId INT);

-- Fill Data
INSERT INTO @Books (Id, Title)
VALUES (0, 'Title 0'), (1, 'Title 1'), (2, 'Title 2'), (3, 'Title 3'),
       (4, 'Title 4'), (5, 'Title 5'), (6, 'Title 6'), (7, 'Title 7'),
       (8, 'Title 8'), (9, 'Title 9'), (10, 'Title 10'), (11, 'Title 11'),
       (12, 'Title 12'), (13, 'Title 13'), (14, 'Title 14'), (15, 'Title 15'),
       (16, 'Title 16'), (17, 'Title 17'), (18, 'Title 18'), (19, 'Title 19'),
       (20, 'Title 20');

INSERT INTO @Reference1 (BookId)
VALUES (0), (2), (4), (6), (8), (10), (12), (14), (16), (18), (20);

INSERT INTO @Reference2 (BookId)
VALUES (0), (3), (6), (9), (12), (15), (18);

INSERT INTO @Reference2 (BookId)
VALUES (0), (5), (10), (15), (20);

This creates three tables and fill the data in it. Each reference table contains some references to the @Books table. As I understand your question you want to have all id's that are not referenced by any of the @ReferenceX tables.

You can do this with a Left Join this way

-- Query 1
SELECT b.Id 
FROM @Books b
  LEFT JOIN @Reference1 r1 ON r1.BookId = b.Id
  LEFT JOIN @Reference2 r2 ON r2.BookId = b.Id
  LEFT JOIN @Reference3 r3 ON r3.BookId = b.Id
WHERE r1.BookId IS NULL 
  AND r2.BookId IS NULL
  AND r3.BookId IS NULL

Second possibillity is with EXCEPT (I guess it usually takes more resources)

-- Query 2
SELECT Id
FROM @Books

EXCEPT

SELECT BookId
FROM @Reference1

EXCEPT

SELECT BookId
FROM @Reference2

EXCEPT

SELECT BookId
FROM @Reference3

Both of them gives the same result:

Id
-----------
1
7
11
13
17
19

This Id are not referenced in any of the @ReferenceX tables.

| improve this answer | |
  • This is correct if you know the reference tables name (or if they are limited) but if the number of tables are huge then will have to write the join conditions for all of them. There is an option of sys.foreign_key_columns. So how can we achieve this using sys tables. – Rishi May 3 '19 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.