SQL:如果Object_ID不是null Alter Table Drop约束VS，则如果存在Alter Table

Question

我试图弄清楚这两种方法是否用于检查存在，然后放下约束完全相同，或者每个方法是否在结果中给出某种差异。下面的代码：

方法1：

if OBJECT_ID('fk_Copy_Item', 'F') is not null
    alter table Rentals.Copy
    drop constraint fk_Copy_Item;
go

方法2：

if exists 
(
    select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where CONSTRAINT_SCHEMA = 'Rentals'
    and CONSTRAINT_NAME = 'fk_Copy_Item'
    and CONSTRAINT_TYPE = 'foreign key'
)
alter table Rentals.Copy
    drop constraint fk_Copy_Item;
go

谢谢！

Answer 1

是的，它们是相同的。OBJECT_ID()函数针对sys.objects表(MSDN文档(查询，并且INFORMATION_SCHEMA.TABLE_CONSTRAINTS只是相对的视图：

CREATE VIEW INFORMATION_SCHEMA.TABLE_CONSTRAINTS
AS
    SELECT DB_NAME()                AS CONSTRAINT_CATALOG,
           SCHEMA_NAME(c.schema_id) AS CONSTRAINT_SCHEMA,
           c.NAME                   AS CONSTRAINT_NAME,
           DB_NAME()                AS TABLE_CATALOG,
           SCHEMA_NAME(t.schema_id) AS TABLE_SCHEMA,
           t.NAME                   AS TABLE_NAME,
           CASE c.type
             WHEN 'C ' THEN 'CHECK'
             WHEN 'UQ' THEN 'UNIQUE'
             WHEN 'PK' THEN 'PRIMARY KEY'
             WHEN 'F ' THEN 'FOREIGN KEY'
           END                      AS CONSTRAINT_TYPE,
           'NO'                     AS IS_DEFERRABLE,
           'NO'                     AS INITIALLY_DEFERRED
      FROM sys.objects c
           LEFT JOIN sys.tables t ON t.object_id = c.parent_object_id
     WHERE c.type IN ('C', 'UQ', 'PK', 'F')