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_keys
comando 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: suppliers
e 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_groups
as suppliers
tabelas e é um para muitos. Em outras palavras, para cada linha da suppliers
tabela, existe uma linha correspondente na supplier_groups
tabela.
Atualmente, não há como impedir que você adicione uma linha à suppliers
tabela sem uma linha correspondente na supplier_groups
tabela.
Além disso, você pode remover uma linha da supplier_groups
tabela sem excluir ou atualizar as linhas correspondentes da suppliers
tabela. Isso pode deixar linhas órfãs na suppliers
tabela.
Para impor o relacionamento entre as linhas na tabela suppliers
e supplier_groups
, você usa as restrições de chave estrangeira .
Para adicionar a restrição de chave estrangeira à suppliers
tabela, altere a definição da CREATE TABLE
instruçã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_groups
tabela é chamada de tabela pai , que é a tabela à qual uma chave estrangeira faz referência. A suppliers
tabela é conhecida como tabela filha , que é a tabela à qual se aplica a restrição de chave estrangeira.
A group_id
coluna da supplier_groups
tabela é 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_id
coluna da suppliers
tabela é 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_groups
tabela.
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 suppliers
tabela com o grupo de fornecedores que existe na supplier_groups
tabela.
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 suppliers
tabela com o grupo de fornecedores que não existe na supplier_groups
tabela.
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_groups
tabela? Todas as linhas correspondentes da suppliers
tabela 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 DELETE
ou ON UPDATE
da 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 DELETE
que 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 suppliers
usando a SET NULL
ação para a group_id
chave 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 suppliers
tabela:
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_groups
tabela:
DELETE FROM supplier_groups
WHERE group_id = 3;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Quarto, consulte os dados da suppliers
tabela.
SELECT * FROM suppliers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Os valores da group_id
coluna das linhas correspondentes na suppliers
tabela são definidos como NULL.
CONJUNTO PADRÃO
A SET DEFAULT
açã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_id
são padronizados como NULL, se você excluir uma linha da supplier_groups
tabela, os valores de group_id
serã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 RESTRICT
ação não permite alterar ou excluir valores na chave pai da tabela pai.
Primeiro, elimine e crie a suppliers
tabela com a RESTRICT
açã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 suppliers
com 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_groups
tabela:
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 suppliers
tabela que possui group_id
1:
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_groups
tabela:
DELETE FROM supplier_groups
WHERE group_id = 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
SEM AÇÃO
Isso NO ACTION
não significa ignorar a restrição de chave estrangeira. Tem o efeito semelhante ao RESTRICT
.
CASCATA
A CASCADE
ação propaga as alterações da tabela pai para a tabela filho quando você atualiza ou exclui a chave pai.
Primeiro, insira os supplier
grupos na supplier_groups
tabela:
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 suppliers
com a CASCADE
açã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_id
do Domestic
grupo 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_id
coluna da XYZ Corp
tabela suppliers
mudou de 1 para 100 quando atualizamos a group_id
tabela suplier_groups
. Este é o resultado da ON UPDATE CASCADE
ação.
Sexto, exclua o ID 2 do grupo de fornecedores da supplier_groups
tabela:
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_id
valor é 2 foi excluído quando o ID do grupo de fornecedores 2 foi removido da supplier_groups
tabela. Este é o efeito da ON DELETE CASCADE
ação.
Neste tutorial, você aprendeu sobre a restrição de chave estrangeira SQLite e como usá-la para impor o relacionamento entre tabelas relacionadas.