CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Lijo Varghese
Lijo Varghese

Reputation: 21

How to check if a column value is referred in some other table as that column is a foreign key in other table (sql server)?

I have a table that its primary key "ID" field is used in many other table as foreign key.

How can I check that a particular record from this table (for example first record "ID = 1") is used in other table?

If a particular record is used in some other table I don't want to do any operations on that row.

Upvotes: 1

Views: 9038

Answers (6)

Edwin Stoteler
Edwin Stoteler

Reputation: 1228

Use the following if you do not wish to use a trial and error method: 

DECLARE @schema NVARCHAR(20)
DECLARE @table NVARCHAR(50)
DECLARE @column NVARCHAR(50)
DECLARE @SQL NVARCHAR(1000)
DECLARE @ID INT
DECLARE @exists INT
DECLARE @x NVARCHAR(100)

SELECT @x = '@exists int output', @ID = 1, @schema = 'dbo', @table = 'Gebruiker', @column = 'GebruikerHasGebruiker_id'

SELECT @SQL = 'SELECT @exists = 1 WHERE EXISTS( ' + STUFF((
    SELECT ' UNION ALL SELECT ' + U2.COLUMN_NAME + ' AS ID FROM ' +  U2.TABLE_SCHEMA + '.' + U2.TABLE_NAME + ' WHERE ' + U2.COLUMN_NAME + ' = ' + cast(@id as VARCHAR(10))
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON R.UNIQUE_CONSTRAINT_NAME = U.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U2 ON R.CONSTRAINT_NAME = U2.CONSTRAINT_NAME
    WHERE U.TABLE_SCHEMA = @schema
    AND U.TABLE_NAME = @table
    AND U.COLUMN_NAME = @column
    FOR XML PATH('')
    ),1,11, '') + ')'

EXEC sp_executesql @SQL, @x, @exists = @exists OUTPUT

IF 1 <> @exists
BEGIN
  -- do you stuff here
END

But in 99% of the cases you could do this, it's overkill. It is faster if you already know the FKs and just create a query.

Edit: A little explanation. This dynamic SQL looks in the INFORMATION SCHEMA to see all relations with other tables. It uses that information to create a query to check if your ID exists in that table. With a UNION it adds all results and returns 1 if any results are found. This can be used for any database, for any column, as long as you don't check for a FK over multiple columns.

Upvotes: 1

JaMeEL
JaMeEL

Reputation: 56

The Approach should be to collect all the dependent objects and query them to check if the parent tables records exists.

i use a Procedure which returns the dependent objects. The Reason i can not post that procedure is exceeding the limited number 30000 characters to post it is 48237 characters. let me know your mail-id i will send you the procedure.

Iterate through the result of the procedure to check if any dependent column holds your primary tables data.

Upvotes: 0

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95761

On the surface, your question doesn't make sense. Let's look at some data.

users
user_id  user_email
--
1        [email protected]
2        [email protected]

user_downloads
user_id  filename  downloaded_starting
1        123.pdf   2013-05-29 08:00:13
1        234.pdf   2013-05-29 08:05:27
1        345.pdf   2013-05-29 08:10:33

There's a foreign key on user_downloads: foreign key (user_id) references users (user_id).

As long as you don't also declare that foreign key as ON DELETE CASCADE, then you can't delete the corresponding row in users. You don't have to check for the presence of rows in other tables, and you shouldn't. In a big system, that might mean checking hundreds of tables.

If you don't declare the foreign key as ON UPDATE CASCADE, you can't update the user_id if it's referenced by any other table. So, again, you don't have to check.

If you use the email address as the target for a foreign key reference, then, once again, don't use ON DELETE CASCADE and don't use ON UPDATE CASCADE. Don't use those declarations, and you don't have to check. If you don't use the email address as the target for a foreign key reference, it doesn't make sense to prevent updates to it.

So if you build your tables right, you don't have to check any of that stuff.

Upvotes: 2

Igor Borisenko
Igor Borisenko

Reputation: 3866

Using this solution you don't need to hard code all referenced tables. 

use tempdb
 go
/* provide test data*/
if OBJECT_ID(N't2') is not null
    drop table t2
if OBJECT_ID(N't1') is not null
    drop table t1

create table t1(i int  not null primary key)
create table t2(i int not null, constraint fk_t1_t2 foreign key (i) references t1(i))

go

insert into t1 values(1),(2)
insert into t2 values(1)
/* checking if the primary key value referenced in other tables */
declare @forCheck int=1 /* id to be checked if it referenced in other tables */
declare @isReferenced bit=0

begin tran
    begin try
        delete from t1 where i=@forCheck
    end try
    begin catch
        set @isReferenced=1
    end catch

rollback

select @isReferenced

Upvotes: 0

Ian P
Ian P

Reputation: 1724

You could use a trigger to roll back any transaction that gives a true for

"where exists( select * from otherTable Where fk = id union select * from anotherTable Where fk = id union etc)

It wont be too heavy if you have any index on each of the tables which starts with fk, (which you should have for general speed anyway), SQL will just check the index for the id. ie a single read for each table checked.

Upvotes: 1

Yohan Danvin
Yohan Danvin

Reputation: 910

Very blunt solution:

  • try to delete the record.
  • If you get an integrity contraint violation, this means it's referenced by another record, catch this exception
  • If the delete worked, rollback your delete

I said it was blunt :)

Upvotes: 2

Related Questions