Resumo : neste tutorial, você aprenderá como usar a restrição de chave estrangeira SQLite para impor os relacionamentos entre tabelas relacionadas.
Suporte a restrição de chave estrangeira SQLite
SQLite suporta restrição de chave estrangeira desde a versão 3.6.19. A biblioteca SQLite também não deve ser compilada com SQLITE_OMIT_FOREIGN_KEY nem SQLITE_OMIT_TRIGGER .
Para verificar se sua versão atual do SQLite suporta restrições de chave estrangeira ou não, use o seguinte comando.
PRAGMA foreign_keys;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O comando retorna um valor inteiro: 1: habilitado, 0: desabilitado. Se o comando não retornar nada, significa que sua versão SQLite não suporta restrições de chave estrangeira.
Se a biblioteca SQLite for compilada com suporte a restrições de chave estrangeira, o aplicativo poderá usar o PRAGMA foreign_keyscomando para ativar ou desativar restrições de chave estrangeira em tempo de execução.
Para desativar a restrição de chave estrangeira:
PRAGMA foreign_keys = OFF;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Para ativar a restrição de chave estrangeira:
PRAGMA foreign_keys = ON;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Introdução às restrições de chave estrangeira do SQLite
Vamos começar com duas tabelas: supplierse supplier_groups:
CREATE TABLE suppliers (
supplier_id integer PRIMARY KEY,
supplier_name text NOT NULL,
group_id integer NOT NULL
);
CREATE TABLE supplier_groups (
group_id integer PRIMARY KEY,
group_name text NOT NULL
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Supondo que cada fornecedor pertença a um e apenas um grupo de fornecedores. E cada grupo de fornecedores pode ter zero ou muitos fornecedores. O relacionamento entre supplier_groupsas supplierstabelas e é um para muitos. Em outras palavras, para cada linha da supplierstabela, existe uma linha correspondente na supplier_groupstabela.
Atualmente, não há como impedir que você adicione uma linha à supplierstabela sem uma linha correspondente na supplier_groupstabela.
Além disso, você pode remover uma linha da supplier_groupstabela sem excluir ou atualizar as linhas correspondentes da supplierstabela. Isso pode deixar linhas órfãs na supplierstabela.
Para impor o relacionamento entre as linhas na tabela supplierse supplier_groups, você usa as restrições de chave estrangeira .
Para adicionar a restrição de chave estrangeira à supplierstabela, altere a definição da CREATE TABLEinstrução acima da seguinte forma:
DROP TABLE suppliers;
CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER NOT NULL,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A supplier_groupstabela é chamada de tabela pai , que é a tabela à qual uma chave estrangeira faz referência. A supplierstabela é conhecida como tabela filha , que é a tabela à qual se aplica a restrição de chave estrangeira.
A group_idcoluna da supplier_groupstabela é chamada de chave pai , que é uma coluna ou um conjunto de colunas na tabela pai à qual a restrição de chave estrangeira faz referência. Normalmente, a chave pai é a chave primária da tabela pai.
A group_idcoluna da supplierstabela é chamada de chave filha. Geralmente, a chave filha faz referência à chave primária da tabela pai.
Exemplo de restrição de chave estrangeira SQLite
Primeiro, insira três linhas na supplier_groupstabela.
INSERT INTO supplier_groups (group_name)
VALUES
('Domestic'),
('Global'),
('One-Time');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, insira um novo fornecedor na supplierstabela com o grupo de fornecedores que existe na supplier_groupstabela.
INSERT INTO suppliers (supplier_name, group_id)
VALUES ('HP', 2);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Esta afirmação funciona perfeitamente bem.
Terceiro, tente inserir um novo fornecedor na supplierstabela com o grupo de fornecedores que não existe na supplier_groupstabela.
INSERT INTO suppliers (supplier_name, group_id)
VALUES('ABC Inc.', 4);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )SQLite verificou a restrição de chave estrangeira, rejeitou a alteração e emitiu a seguinte mensagem de erro:
[SQLITE_CONSTRAINT] Abort due to constraint violation (FOREIGN KEY constraint failed)Linguagem de código: CSS ( css )Ações de restrição de chave estrangeira SQLite
O que aconteceria se você excluísse uma linha da supplier_groupstabela? Todas as linhas correspondentes da supplierstabela também devem ser excluídas? As mesmas perguntas para a operação de atualização .
Para especificar como a restrição de chave estrangeira se comporta sempre que a chave pai é excluída ou atualizada, use a ação ON DELETEou ON UPDATEda seguinte maneira:
FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )SQLite suporta as seguintes ações:
- DEFINIR NULO
- CONJUNTO PADRÃO
- RESTRINGIR
- SEM AÇÃO
- CASCATA
Na prática, os valores da chave primária na tabela pai não mudam, portanto as regras de atualização são menos importantes. A regra mais importante é aquela DELETEque especifica a ação quando a chave pai é excluída.
Examinaremos cada ação pelo exemplo a seguir
DEFINIR NULO
Quando a chave pai é alterada, excluída ou atualizada, as chaves filhas correspondentes de todas as linhas da tabela filha são definidas como NULL.
Primeiro, elimine e crie a tabela suppliersusando a SET NULLação para a group_idchave estrangeira:
DROP TABLE suppliers;
CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
ON UPDATE SET NULL
ON DELETE SET NULL
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, insira algumas linhas na supplierstabela:
INSERT INTO suppliers (supplier_name, group_id)
VALUES('XYZ Corp', 3);
INSERT INTO suppliers (supplier_name, group_id)
VALUES('ABC Corp', 3);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, exclua o ID do grupo de fornecedores 3 da supplier_groupstabela:
DELETE FROM supplier_groups
WHERE group_id = 3;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Quarto, consulte os dados da supplierstabela.
SELECT * FROM suppliers;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Os valores da group_idcoluna das linhas correspondentes na supplierstabela são definidos como NULL.
CONJUNTO PADRÃO
A SET DEFAULTação define o valor da chave estrangeira para o valor padrão especificado na definição da coluna quando você cria a tabela .
Como os valores na coluna group_idsão padronizados como NULL, se você excluir uma linha da supplier_groupstabela, os valores de group_idserão definidos como NULL.
Depois de atribuir o valor padrão, a restrição de chave estrangeira entra em ação e realiza a verificação.
RESTRINGIR
A RESTRICTação não permite alterar ou excluir valores na chave pai da tabela pai.
Primeiro, elimine e crie a supplierstabela com a RESTRICTação na chave estrangeira group_id:
DROP TABLE suppliers;
CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, insira uma linha na tabela supplierscom group_id 1.
INSERT INTO suppliers (supplier_name, group_id)
VALUES('XYZ Corp', 1);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, exclua o grupo de fornecedores com id 1 da supplier_groupstabela:
DELETE FROM supplier_groups
WHERE group_id = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )SQLite emitiu o seguinte erro:
[SQLITE_CONSTRAINT] Abort due to constraint violation (FOREIGN KEY constraint failed)Linguagem de código: CSS ( css )Para corrigir isso, você deve primeiro excluir todas as linhas da supplierstabela que possui group_id1:
DELETE FROM suppliers
WHERE group_id =1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Em seguida, você pode excluir o grupo de fornecedores 1 da supplier_groupstabela:
DELETE FROM supplier_groups
WHERE group_id = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )SEM AÇÃO
Isso NO ACTIONnão significa ignorar a restrição de chave estrangeira. Tem o efeito semelhante ao RESTRICT.
CASCATA
A CASCADEação propaga as alterações da tabela pai para a tabela filho quando você atualiza ou exclui a chave pai.
Primeiro, insira os suppliergrupos na supplier_groupstabela:
INSERT INTO supplier_groups (group_name)
VALUES
('Domestic'),
('Global'),
('One-Time');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, elimine e crie a tabela supplierscom a CASCADEação na chave estrangeira group_id:
DROP TABLE suppliers;
CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, insira alguns fornecedores na tabela suppliers:
INSERT INTO suppliers (supplier_name, group_id)
VALUES('XYZ Corp', 1);
INSERT INTO suppliers (supplier_name, group_id)
VALUES('ABC Corp', 2);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quarto, atualização group_iddo Domesticgrupo de fornecedores para 100:
UPDATE supplier_groups
SET group_id = 100
WHERE group_name = 'Domestic';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Quinto, consulte os dados da tabela suppliers:
SELECT * FROM suppliers;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode ver, o valor na group_idcoluna da XYZ Corptabela suppliersmudou de 1 para 100 quando atualizamos a group_idtabela suplier_groups. Este é o resultado da ON UPDATE CASCADEação.
Sexto, exclua o ID 2 do grupo de fornecedores da supplier_groupstabela:
DELETE FROM supplier_groups
WHERE group_id = 2;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Sétimo, consulte os dados da tabela suppliers:
SELECT * FROM suppliers;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
O ID do fornecedor 2 cujo group_idvalor é 2 foi excluído quando o ID do grupo de fornecedores 2 foi removido da supplier_groupstabela. Este é o efeito da ON DELETE CASCADEação.
Neste tutorial, você aprendeu sobre a restrição de chave estrangeira SQLite e como usá-la para impor o relacionamento entre tabelas relacionadas.
