1  

If Exists ( Select * From OtherTable
            Where OtherTableFKColumn = MainTablePrimaryKey) 
   Begin
       Rollback Transaction
       RaisError('Violating FK Constraint in Table [OtherTable]', 16, 1)
   End

1  

Other than checking the COUNT(*) of every related table? I don't think so.

除了检查每个相关表的COUNT(*)之外?我不这么认为。

0  

One ugly attempt would be to try a DELETE in a transaction and then force a ROLLBACK if it is successful. But this is to dirty for my taste.

一个丑陋的尝试是在事务中尝试DELETE,然后在成功时强制ROLLBACK。但这对我来说很脏。

0  

This is a question that on the surface looks good, but has implications.

这是一个表面看起来不错但有影响的问题。

First of all, you'd need to ensure that after you've read the status of those relations, nobody could change those, so obviously you need to use a transaction and lock the rows in question.

首先,您需要确保在阅读完这些关系的状态后,没有人可以更改这些关系,因此显然您需要使用事务并锁定有问题的行。

Then you need a way to figure out what relations to check, as I see in a comment here your question about what happens if someone later adds a new relation. So you need to query the schema, or perhaps auto-generate this code from the schema, so that the detection mechanism only needs to run each time you change the schema.

然后你需要一种方法来弄清楚要检查的关系,正如我在评论中看到的那样,你的问题是如果有人后来添加了新关系会发生什么。因此,您需要查询架构,或者可能从架构中自动生成此代码,以便每次更改架构时都只需要运行检测机制。

Now, does the exception you get really seem that expensive after this ordeal?

现在,在这场考验之后你得到的例外真的看起来很贵吗?

0  

I don't think it's a good idea to attempt something like this because it means that every foreign key has to be checked twice: once by you beforehand, and then again by the server when you execute your SQL. The performance implications could be severe.

我认为尝试这样的事情并不是一个好主意,因为这意味着必须对每个外键进行两次检查:事先由您执行一次,然后在执行SQL时由服务器再次检查。性能影响可能很严重。

However, if you have your mind set on doing this, the most generic way involves using the database's data dictionary. I am not familiar with the SQL Server data dictionary, but other relational databases store all their metadata in database tables that you can query. You could find all the foreign keys that reference your table and dynamically build queries which look for dependent rows.

但是,如果您有这样的想法,最通用的方法是使用数据库的数据字典。我不熟悉SQL Server数据字典,但其他关系数据库将所有元数据存储在您可以查询的数据库表中。您可以找到引用表的所有外键,并动态构建查找依赖行的查询。