如何检查列值是否在其他表中引用，因为该列是其他表（sql server）中的外键？

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

我有一个表，其主键“ID”字段在许多其他表中用作外键。

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

如何检查此表中的特定记录（例如第一个记录“ID = 1”）是否在其他表中使用？

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

如果在某个其他表中使用了特定记录，我不想对该行执行任何操作。

6 个解决方案

#1

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

从表面上看，你的问题没有意义。我们来看看一些数据。

users
user_id  user_email
--
1        abc@def.com
2        def@hij.com

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).

user_downloads上有一个外键：外键（user_id）引用用户（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.

只要您不将外键声明为ON DELETE CASCADE，则无法删除用户中的相应行。您不必检查其他表中是否存在行，您不应该这样做。在一个大系统中，这可能意味着检查数百个表。

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.

如果未将外键声明为ON UPDATE CASCADE，则如果user_id被任何其他表引用，则无法更新该user_id。所以，再次，你不必检查。

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.

如果您使用电子邮件地址作为外键引用的目标，则再次使用ON DELETE CASCADE并且不要使用ON UPDATE CASCADE。不要使用这些声明，也不必检查。如果您不使用电子邮件地址作为外键引用的目标，则阻止对其进行更新是没有意义的。

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

因此，如果您正确构建表，则无需检查任何内容。

#2

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 :)

我说这是生硬的:)

#3

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

您可以使用触发器回滚任何给出true的事务

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

“where where（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.

如果你在每个以fk开头的表上都有任何索引（不管怎样你应该具有一般速度），它不会太沉重，SQL只会检查索引的id。即检查每个表的单个读数。

#4

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.

但是在99％的情况下你可以做到这一点，这太过分了。如果您已经知道FK并且只是创建查询，则会更快。

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.

编辑：一点解释。此动态SQL在信息模式中查看与其他表的所有关系。它使用该信息创建查询以检查您的ID是否存在于该表中。使用UNION，它会添加所有结果，如果找到任何结果，则返回1。只要不检查多列上的FK，这可以用于任何列的任何数据库。

#5

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

#6

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.

我使用一个返回依赖对象的过程。我无法发布该程序的原因是超过限定数量30000个字符发布它是48237个字符。让我知道你的邮件ID我将把程序发给你。

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

迭代过程的结果以检查是否有任何依赖列保存主表数据。

#1