Simple way to find if PK on a record is linked to a FK

[traigo]

We have a web app that lets users create records in a table, then assign those to other records using primary keys and foreign keys. If a user tries to delete one of the master records while it's linked using a FK, it will error. I have it catching this error now, but I'd like to deny deleting the record if it's linked. I want them to still be able to delete records not in use. Is there a simple way to return this in the query?

Table1
id int identity PK
field1 varchar
field2 varchar

Table 2
id int identity PK
Table1_id int FK
field1 varchar
field2 varchar

Select id,field1,field2,??inuse?? from Table1

So when I return the results from this query to the page, I can disable the delete function if inuse = 1.

There are 10 types of people in the world, those that understand binary, and those that don't.

 

[mrdenny]

You would need to write a function to read the other table.

CREATE FUNCTION dbo.HasChildren (@RecordId INT)
RETURNS BIT
AS
BEGIN
     DECLARE @HasChildren BIT

     IF EXISTS (SELECT * FROM ChildTable WHERE OtherId = @RecordId)
          SET @HasChildren = 1
     ELSE
          SET @HasChildren = 0

RETURN @HasChildren
END
GO

Then call the function as part of your SELECT statement.

SELECT Col1, Col2, dbo.HasChildren(PrimaryKey)
FROM ParentTable

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[traigo]

I gave a simplified example. In reality, one of our master tables is linked to no fewer than 30 tables, with more to be added as we expand the application. That's also true for a number of our master tables. I was hoping there was a built-in function of which I wasn't aware that would tell me. As for building my own function, I would have to modify it each time I added a new table.

Would it be possible to:
pass a table name and record ID to a function
the function would query the schema and get the PK column
then query the schema to get all foreign key tables and FK columns
then iteratively query each table and return 1 and exit if any of them returns any records otherwise return 0

There are 10 types of people in the world, those that understand binary, and those that don't.

 

[mrdenny]

You could, but a query like this is going to become quite expensive very quickly as you add more data to the system.

A better bet would be to put TRY CATCH code around the delete, and simply return a message to the user in the event that the record can't be deleted.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[traigo]

I'm catching the SQLException now and returning a message that it can't be deleted because it's in use. I just wanted to show them that they can't delete it before they try.

There are 10 types of people in the world, those that understand binary, and those that don't.