Início / dba / Perguntas / 20168
Accepted
8kb
Asked: 2012-06-29 16:06:14 +0800 CST

Como o SQL Server escolhe uma chave de índice para uma referência de chave estrangeira?

  • 772

Estou trabalhando com um banco de dados herdado que foi importado do MS Access. Existem cerca de vinte tabelas com chaves primárias exclusivas não agrupadas que foram criadas durante a atualização do MS Access > SQL Server.

Muitas dessas tabelas também têm índices exclusivos e não agrupados que são duplicatas da chave primária.

Estou tentando limpar isso.

Mas o que descobri é que depois de recriar as chaves primárias como índices clusterizados e, em seguida, tentar reconstruir a chave estrangeira, a chave estrangeira está referenciando o antigo índice duplicado (que era exclusivo).

Eu sei disso porque não vai me deixar descartar os índices duplicados.

Eu acho que o SQL Server sempre escolheria uma chave primária, se existisse. O SQL Server tem um método de escolha entre um índice exclusivo e uma chave primária?

Para duplicar o problema (no 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

Mensagem de erro:

Msg 3723, Nível 16, Estado 6, Linha 36 Um DROP INDEX explícito não é permitido no índice 'Parent.IX_Parent'. Ele está sendo usado para imposição de restrição FOREIGN KEY.

sql-server index

2 respostas

  1. Best Answer

    A (falta de) documentação sugere que esse comportamento é um detalhe de implementação e, portanto, indefinido e sujeito a alterações a qualquer momento.

    Isso contrasta fortemente com CREATE FULLTEXT INDEX , onde você deve especificar o nome de um índice para anexar - AFAIK, não há FOREIGN KEYsintaxe não documentada para fazer o equivalente (embora teoricamente possa haver no futuro).

    Conforme mencionado, faz sentido que o SQL Server escolha o menor índice físico ao qual associar a chave estrangeira. Se você alterar o script para criar a restrição exclusiva como CLUSTERED, o script "funcionará" no 2008 R2. Mas esse comportamento ainda está indefinido e não deve ser considerado.

    Como acontece com a maioria dos aplicativos legados, você só precisa ir direto ao ponto e limpar as coisas.

    • 7

  2. O SQL Server tem um método de escolha entre um índice exclusivo e uma chave primária?

    Pelo menos é possível direcionar o SqlServer para fazer referência à chave primária, quando a chave estrangeira está sendo criada e existem restrições de chave alternativas ou índices exclusivos na tabela que está sendo referenciada.

    Se a chave primária precisar ser referenciada, apenas o nome da tabela referenciada deve ser especificado na definição da chave estrangeira e a lista de colunas referenciadas deve ser omitida:

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

    Mais detalhes abaixo.

    Considere a seguinte configuração:

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

    onde table TRefpretende referenciar table T.

    Para criar restrição referencial pode-se usar ALTER TABLEo comando com duas alternativas:

    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;

    observe que no segundo caso nenhuma coluna da tabela sendo referenciada é especificada ( REFERENCES Tversus REFERENCES T (id)).

    Como ainda não há índices de chave ativados T, a execução desses comandos gerará erros.

    O primeiro comando retorna o seguinte erro:

    Msg 1776, Nível 16, Estado 0, Linha 4

    Não há chaves primárias ou candidatas na tabela referenciada 'T' que correspondam à lista de colunas de referência na chave estrangeira 'FK_TRef_T_1'.

    O segundo comando, no entanto, retorna um erro diferente:

    Msg 1773, Nível 16, Estado 0, Linha 4

    A chave estrangeira 'FK_TRef_T_2' possui referência implícita ao objeto 'T' que não possui uma chave primária definida.

    veja que, no primeiro caso, a expectativa é primária ou chaves candidatas , enquanto no segundo caso, a expectativa é apenas a chave primária .

    Vamos verificar se o SqlServer usará algo diferente da chave primária com o segundo comando ou não.

    Se adicionarmos alguns índices exclusivos e chaves exclusivas em 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);

    comando para FK_TRef_T_1criação é bem-sucedido, mas o comando para FK_TRef_T_2criação ainda falha com Msg 1773.

    Finalmente, se adicionarmos a chave primária em T:

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

    comando para FK_TRef_T_2criação é bem-sucedido.

    Vamos verificar quais índices da tabela Tsão referenciados pelas chaves estrangeiras da tabela 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');

    isso retorna:

    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

    veja que FK_TRef_T_2correspondem a PK_T.

    Portanto, sim, com o uso da REFERENCES Tsintaxe, a chave estrangeira de TRefé mapeada para a chave primária de T.

    Não consegui encontrar esse comportamento descrito diretamente na documentação do SqlServer, mas a Msg 1773 dedicada sugere que não é acidental. Provavelmente tal implementação fornece conformidade com o padrão SQL, abaixo está um pequeno trecho da seção 11.8 da ANSI/ISO 9075-2:2003

    11 Definição e manipulação do esquema

    11.8 <definição de restrição referencial>

    Função
    Especifique uma restrição referencial.

    Formato

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

    Regras de sintaxe
    ...
    3) Caso:
    ...
    b) Se a <tabela e colunas referenciadas> não especificar uma <lista de colunas de referência>, então o descritor da tabela referenciada deve incluir uma restrição única que especifique PRIMARY KEY. Deixe as colunas referenciadas serem a coluna ou colunas identificadas pelas colunas exclusivas nessa restrição exclusiva e deixe a coluna referenciada ser uma dessas colunas. A <tabela e colunas referenciadas> deve ser considerada como especificando implicitamente uma <lista de colunas de referência> que seja idêntica àquela <lista de coluna única>.
    ...

    O Transact-SQL oferece suporte e estende o ANSI SQL. No entanto, ele não está exatamente em conformidade com o SQL Standard. Há um documento chamado SQL Server Transact-SQL ISO/IEC 9075-2 Standards Support Document (MS-TSQLISO02 em resumo, consulte aqui ) que descreve o nível de suporte fornecido pelo Transact-SQL. O documento lista as extensões e variações do padrão. Por exemplo, ele documenta que a MATCHcláusula não é suportada na definição de restrição referencial. Mas não há variações documentadas relevantes para o padrão citado. Então, minha opinião é que o comportamento observado é documentado o suficiente.

    E com o uso da REFERENCES T (<reference column list>)sintaxe, parece que o SqlServer seleciona o primeiro índice não clusterizado adequado entre os índices da tabela que está sendo referenciada (aquela com menos index_idaparência, não aquela com o menor tamanho físico, conforme assumido nos comentários da pergunta) ou índice clusterizado, se for ternos e não há índices não clusterizados adequados. Tal comportamento parece ser consistente desde o SqlServer 2008 (versão 10.0). Isso é apenas observação, claro, sem garantias neste caso.

    • 3

relate perguntas