Início / dba / Perguntas / 345047
Accepted
user209974
Asked: 2025-02-02 01:38:05 +0800 CST

Como isso é um recurso do sqlite3? Relacionamento de chave estrangeira deu errado

  • 772

Pegue o seguinte código:

pragma foreign_keys = ON;

create table people(people_id integer primary key, name text not null);
insert into people (name) values ("Mom"), ("Jack the Ripper");

create table family_member(people_id integer primary key references people(people_id));

insert into family_member values ((select people_id from people where name = "Mom"));
insert into family_member values ((select people_id from people where name = "Dad"));  -- silent error here

select name from family_member inner join people using (people_id);
-- Jack the Ripper is part of the family

Como isso é legítimo no sqlite? Nem mesmo um aviso é gerado. Isso é por boas razões, por razões de legado, há algo na documentação sobre isso que eu não encontrei? Para mim, isso é preocupante e acredito que nenhum outro SQL DB se comporte dessa forma.

sqlite

1 respostas

  1. Best Answer

    INTEGER PRIMARY KEYcolunas no SQLite são um tanto estranhas. Elas são tomadas por padrão para significar um ROWIDou AUTOINCREMENT, ou em outras linguagens que seria SERIALou IDENTITY.

    Dos documentos :

    Com uma exceção observada abaixo, se uma tabela rowid tiver uma chave primária que consiste em uma única coluna e o tipo declarado dessa coluna for "INTEGER" em qualquer mistura de maiúsculas e minúsculas, então a coluna se tornará um alias para o rowid.
    ...
    uma instrução INSERT pode fornecer um valor para usar como o rowid para cada linha inserida. Diferentemente das colunas SQLite normais, uma chave primária inteira ou coluna rowid deve conter valores inteiros.
    ...
    Se uma instrução INSERT tentar inserir um valor NULL em uma coluna rowid ou chave primária inteira, o sistema escolherá um valor inteiro para usar como o rowid automaticamente. Uma descrição detalhada de como isso é feito é fornecida separadamente .

    O documento vinculado afirma:

    Se nenhum ROWID for especificado na inserção, ou se o ROWID especificado tiver um valor NULL, então um ROWID apropriado será criado automaticamente. O algoritmo usual é dar à linha recém-criada um ROWID que seja uma unidade maior que o maior ROWID na tabela antes da inserção.

    Agora, combinado com seu uso um tanto estranho de subconsultas, fica claro o que está acontecendo.

    A subconsulta, quando executada sozinha, não retorna nenhuma linha:

    select people_id from people where name = "Dad"

    Quando colocado dentro de uma subconsulta escalar, "no rows" é convertido para um NULL. Então se torna:

    insert into family_member values (NULL);

    O mecanismo então decide criar um valor do nada, que por acaso corresponde a um valor existente na chave estrangeira.

    Também notei sua falta de especificação de quais colunas você está inserindo, o que pode ser um problema para você mais tarde.

    A solução correta é especificar WITHOUT ROWIDna sua tabela. Isso significa que o mecanismo não gerará um valor para essa coluna e, portanto, a inserção falhará. Você também precisa especificar explicitamente a NOT NULLrestrição, porque o SQLite não adiciona isso por padrão na maioria dos casos.

    create table family_member (
    people_id integer not null primary key references people(people_id)
) WITHOUT ROWID;

    Você também deveria ter alterado seu INSERTpara um mais normal INSERT...SELECT..., sem uma subconsulta. Isso teria resultado na inserção de nenhuma linha em primeiro lugar.

    insert into family_member (people_id)
select people_id
from people
where name = 'Mom';

insert into family_member (people_id)
select people_id
from people
where name = 'Dad';
    • 25

relate perguntas