主页 / dba / 问题 / 20168
Accepted
8kb
Asked: 2012-06-29 16:06:14 +0800 CST

SQL Server 如何为外键引用选择索引键？

  • 772

我正在使用从 MS Access 导入的旧数据库。在 MS Access > SQL Server 升级期间创建了大约 20 个具有非群集、唯一主键的表。

其中许多表还具有唯一的非聚集索引，它们是主键的副本。

我正在尝试清理它。

但是我发现在我将主键重新创建为聚集索引之后，然后尝试重建外键，外键引用了旧的重复索引（这是唯一的）。

我知道这一点，因为它不会让我删除重复的索引。

我认为如果存在一个主键，SQL Server 总是会选择一个主键。SQL Server 是否有在唯一索引和主键之间进行选择的方法？

要复制问题（在 SQL Server 2008 R2 上）：

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Child') DROP TABLE Child
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Parent') DROP TABLE Parent
GO

-- Create the parent table
CREATE TABLE Parent (ParentID INT NOT NULL IDENTITY(1,1)) 

-- Make the parent table a heap
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED (ParentID) 

-- Create the duplicate index on the parent table
CREATE UNIQUE NONCLUSTERED INDEX IX_Parent ON Parent (ParentID) 

-- Create the child table
CREATE TABLE Child  (ChildID  INT NOT NULL IDENTITY(1,1), ParentID INT NOT NULL ) 

-- Give the child table a normal PKey
ALTER TABLE Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ChildID) 

-- Create a foreign key relationship with the Parent table on ParentID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to clean this up
-- Drop the foreign key constraint on the Child table
ALTER TABLE Child DROP CONSTRAINT FK_Child

-- Drop the primary key constraint on the Parent table
ALTER TABLE Parent DROP CONSTRAINT PK_Parent

-- Recreate the primary key on Parent as a clustered index
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentID) 

-- Recreate the foreign key in Child pointing to parent ID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to drop the duplicate index on Parent 
DROP INDEX IX_Parent ON Parent

错误信息：

消息 3723，级别 16，状态 6，第 36 行索引“Parent.IX_Parent”上不允许显式 DROP INDEX。它被用于 FOREIGN KEY 约束强制执行。

sql-server index

2 个回答

  1. Best Answer

    （缺乏）文档表明此行为是一个实现细节，因此未定义并且随时可能更改。

    这与 CREATE FULLTEXT INDEX 形成鲜明对比，在CREATE FULLTEXT INDEX中，您必须指定要附加到的索引的名称——AFAIK，没有未记录FOREIGN KEY的语法可以执行等效操作（尽管理论上，将来可能会有）。

    如前所述，SQL Server 选择与外键关联的最小物理索引是有意义的。如果您更改脚本以将唯一约束创建为CLUSTERED，则该脚本在 2008 R2 上“有效”。但是这种行为仍然是未定义的，不应依赖。

    与大多数遗留应用程序一样，您只需要深入了解细节并清理干净即可。

    • 7

  2. SQL Server 是否有在唯一索引和主键之间进行选择的方法？

    至少可以在创建外键并且在被引用的表上确实存在替代键约束或唯一索引时，指示 SqlServer 引用主键。

    如果需要引用主键，则只应在外键定义中指定被引用表的名称，并应省略被引用的列列表：

    ALTER TABLE Child
    ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID)
        -- omit key columns of the referenced table
        REFERENCES Parent /*(ParentID)*/;

    更多详情如下。

    考虑以下设置：

    CREATE TABLE T (id int NOT NULL, a int, b int, c uniqueidentifier, filler binary(1000));
CREATE TABLE TRef (tid int NULL);

    其中 tableTRef打算引用 table T。

    要创建引用约束，可以使用ALTER TABLE具有两种选择的命令：

    ALTER TABLE TRef
    ADD CONSTRAINT FK_TRef_T_1 FOREIGN KEY (tid) REFERENCES T (id);

ALTER TABLE TRef
    ADD CONSTRAINT FK_TRef_T_2 FOREIGN KEY (tid) REFERENCES T;

    请注意，在第二种情况下，没有指定要引用的表的列（REFERENCES Tvs REFERENCES T (id)）。

    由于还没有关键索引T，执行这些命令会产生错误。

    第一个命令返回以下错误：

    消息 1776，第 16 级，状态 0，第 4 行

    被引用表“T”中没有与外键“FK_TRef_T_1”中的引用列列表匹配的主键或候选键。

    但是，第二个命令返回不同的错误：

    消息 1773，第 16 级，状态 0，第 4 行

    外键“FK_TRef_T_2”隐式引用了对象“T”，该对象没有定义主键。

    看到在第一种情况下期望是主键或候选键，而在第二种情况下期望只是主键。

    让我们检查一下 SqlServer 是否会在第二个命令中使用主键以外的东西。

    如果我们在 上添加一些唯一索引和唯一键T：

    CREATE UNIQUE INDEX IX_T_1 on T(id) INCLUDE (filler);
CREATE UNIQUE INDEX IX_T_2 on T(id) INCLUDE (c);
CREATE UNIQUE INDEX IX_T_3 ON T(id) INCLUDE (a, b);

ALTER TABLE T
    ADD CONSTRAINT UQ_T UNIQUE CLUSTERED (id);

    创建命令FK_TRef_T_1成功，但FK_TRef_T_2创建命令仍然失败并显示 Msg 1773。

    最后，如果我们添加主键T：

    ALTER TABLE T
    ADD CONSTRAINT PK_T PRIMARY KEY NONCLUSTERED (id);

    创建命令FK_TRef_T_2成功。

    让我们检查一下 tableT的外键引用了 table 的哪些索引TRef：

    select
    ix.index_id,
    ix.name as index_name,
    ix.type_desc as index_type_desc,
    fk.name as fk_name
from sys.indexes ix
    left join sys.foreign_keys fk on
        fk.referenced_object_id = ix.object_id
        and fk.key_index_id = ix.index_id
        and fk.parent_object_id = object_id('TRef')
where ix.object_id = object_id('T');

    这返回：

    index_id  index_name  index_type_desc   fk_name
--------- ----------- ----------------- ------------
1         UQ_T        CLUSTERED         NULL
2         IX_T_1      NONCLUSTERED      FK_TRef_T_1
3         IX_T_2      NONCLUSTERED      NULL
4         IX_T_3      NONCLUSTERED      NULL
5         PK_T        NONCLUSTERED      FK_TRef_T_2

    看到FK_TRef_T_2对应PK_T。

    所以，是的，使用REFERENCES T语法外键TRef映射到主键T。

    我无法直接找到 SqlServer 文档中描述的这种行为，但专用的 Msg 1773 表明这不是偶然的。此类实现可能符合 SQL 标准，以下是 ANSI/ISO 9075-2:2003 第 11.8 节的简短摘录

    11 模式定义和操作

    11.8 <参照约束定义>

    函数
    指定引用约束。

    格式

    <referential constraint definition> ::=
    FOREIGN KEY <left paren> <referencing columns> <right paren>
        <references specification>

<references specification> ::=
    REFERENCES <referenced table and columns>
    [ MATCH <match type> ]
    [ <referential triggered action> ]
...

    语法规则
    ...
    3) 案例：
    ...
    b) 如果<referenced table and columns> 没有指定<reference column list>，则被引用表的表描述符应包括指定PRIMARY KEY 的唯一约束。让引用的列是由该唯一约束中的唯一列标识的一个或多个列，并且让引用的列 是一个这样的列。<referenced table and columns> 应被认为隐含地指定了一个与那个<unique column list> 相同的<reference column list>。
    ...

    Transact-SQL 支持和扩展 ANSI SQL。然而，它并不完全符合 SQL 标准。有一个名为SQL Server Transact-SQL ISO/IEC 9075-2 标准支持文档（简称 MS-TSQLISO02，请参见此处）的文档描述了 Transact-SQL 提供的支持级别。该文档列出了标准的扩展和变体。例如，它记录MATCH了引用约束定义中不支持该子句。但是没有与引用的标准相关的记录变化。所以，我的观点是观察到的行为有足够的记录。

    并且通过使用REFERENCES T (<reference column list>)语法，似乎 SqlServer 在被引用的表的索引中选择第一个合适的非聚集索引（index_id看起来最少的索引，而不是问题评论中假设的物理大小最小的索引），或者聚集索引，如果它适合并且没有合适的非聚集索引。自 SqlServer 2008（版本 10.0）以来，这种行为似乎是一致的。当然，这只是观察，在这种情况下不能保证。

    • 3

相关问题