为什么使用 OBJECT_ID() 函数找不到外键? [英] Why can I not find a foreign key using the OBJECT_ID() function?

问题描述

我在 MS SQL Server 2012 中有一个奇怪的问题.我正在尝试检查升级脚本中是否已经存在外键.我过去使用系统 OBJECT_ID() 函数来查找表、视图和过程，但是当我尝试使用它来查找外键时，它不起作用.

I have a strange issue in MS SQL Server 2012. I'm trying to check if a foreign key already exist in an upgrade script. I've used the system OBJECT_ID() function in the past to find tables, views and procedures, but when I try to use it to find a foreign key it does not work.

-- This query always returns null
SELECT OBJECT_ID(N'FK_Name', N'F')

-- This query works, returning the object ID for the foreign key
SELECT object_id FROM sys.foreign_keys WHERE name=N'FK_Name'

这个 SO 答案表明我的 OBJECT_ID() 查询应该可以工作.

This SO answer suggests that my OBJECT_ID() query should work.

推荐答案

嗯，可能是您的外键正在查找不在默认模式中的表(可能是 dbo).在这种情况下，除非您指定架构，否则您将看不到 object_id，如下所示:

Well it could be that your foreign key is looking to the table not in default schema (probably dbo). In this case you'll not see object_id until you specify schema, like this:

SELECT OBJECT_ID(N'<schema>.FK_Name', N'F')

实际上，您的数据库中可以有多个具有相同名称的对象，但它们位于不同的架构中.OBJECT_ID(N'FK_Name', N'F') 将返回默认模式中对象的 id.

Actually, you could have multiple objects with the same name in your database, but within different schemas. OBJECT_ID(N'FK_Name', N'F') will return id of object in the default schema.

你可以这样测试:

create schema test
create table test.temp1 (id int primary key)
create table test.temp2 (id int)
go

alter table test.temp2 add constraint FK_temp foreign key(id) references test.temp1(id)

select object_id('FK_temp', 'F')  -- returns null
select object_id('test.FK_temp', 'F') -- returns object id

drop table test.temp2
drop table test.temp1
drop schema test

sql 小提琴演示

这篇关于为什么使用 OBJECT_ID() 函数找不到外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！