Gerenciamento de Restrições no SQLite

Introdução

Neste laboratório, você explorará o gerenciamento de restrições do SQLite para garantir a integridade dos dados. Você começará definindo restrições de chave estrangeira (foreign key) para estabelecer relacionamentos entre tabelas, implementando restrições CHECK, criando chaves compostas e, finalmente, testando violações de restrições para entender como o SQLite impõe essas regras. Essa experiência prática fornecerá uma compreensão prática de como manter a consistência dos dados dentro de seus bancos de dados SQLite.

Criar Tabelas com uma Restrição de Chave Estrangeira

Nesta etapa, você criará duas tabelas, customers e orders, e estabelecerá uma restrição de chave estrangeira entre elas. Essa restrição garantirá que cada pedido esteja associado a um cliente válido.

Primeiro, abra o shell do SQLite executando o seguinte comando em seu terminal:

sqlite3 /home/labex/project/database.db

Este comando abre uma conexão com o arquivo de banco de dados SQLite chamado database.db. Se o arquivo não existir, o SQLite o criará. Você deve agora ver o prompt sqlite>.

Agora, crie a tabela customers com o seguinte comando SQL:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Este comando cria uma tabela chamada customers com quatro colunas: customer_id, first_name, last_name e email. A coluna customer_id é a chave primária (primary key) e será incrementada automaticamente para cada novo cliente.

Em seguida, crie a tabela orders com uma restrição de chave estrangeira referenciando a tabela customers:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Este comando cria uma tabela chamada orders com quatro colunas: order_id, customer_id, order_date e total. A coluna order_id é a chave primária. A cláusula FOREIGN KEY (customer_id) REFERENCES customers(customer_id) estabelece uma restrição de chave estrangeira, garantindo que o customer_id na tabela orders referencie um customer_id válido na tabela customers.

Para verificar se as tabelas foram criadas, você pode usar o seguinte comando:

.tables

Este comando listará todas as tabelas no banco de dados. Você deve ver tanto customers quanto orders na saída.

Inserir Dados e Testar a Restrição de Chave Estrangeira

Nesta etapa, você inserirá dados nas tabelas customers e orders e testará a restrição de chave estrangeira.

Primeiro, você precisa habilitar as restrições de chave estrangeira no SQLite (elas são desabilitadas por padrão):

PRAGMA foreign_keys = ON;

Agora, insira alguns dados na tabela customers:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');

Este comando insere dois novos clientes na tabela customers. O customer_id será atribuído automaticamente.

Agora, insira um pedido na tabela orders, referenciando um dos clientes existentes:

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

Este comando insere um novo pedido na tabela orders, associando-o ao cliente cujo customer_id é 1.

Em seguida, tente inserir um pedido com um customer_id que não existe na tabela customers:

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

Você verá uma mensagem de erro semelhante a esta: Error: FOREIGN KEY constraint failed. Isso confirma que a restrição de chave estrangeira está funcionando corretamente, impedindo que você crie um pedido para um cliente inexistente.

Para visualizar os dados nas tabelas, você pode usar os seguintes comandos:

SELECT * FROM customers;
SELECT * FROM orders;

Esses comandos exibirão o conteúdo das tabelas customers e orders, respectivamente.

Implementar uma Restrição CHECK

Nesta etapa, você criará uma nova tabela customers_with_check que inclui uma restrição CHECK para garantir que todos os endereços de e-mail contenham o símbolo @.

Primeiro, vamos criar uma nova tabela com a restrição CHECK diretamente:

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

Este comando cria uma nova tabela chamada customers_with_check com uma restrição CHECK na coluna email que garante que ela contenha o símbolo @. O operador LIKE é usado para correspondência de padrões (pattern matching), e % é um caractere curinga (wildcard character) que corresponde a qualquer sequência de caracteres.

Agora, vamos copiar os dados existentes dos clientes para a nova tabela:

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

Agora, tente inserir um novo cliente com um endereço de e-mail inválido:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

Você deve ver uma mensagem de erro semelhante a esta: Error: CHECK constraint failed: email. Isso indica que a restrição CHECK impediu a inserção do endereço de e-mail inválido.

Para verificar se a restrição CHECK está funcionando, insira um cliente com um endereço de e-mail válido:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');

Este comando deve ser executado com sucesso, pois o endereço de e-mail contém o símbolo @.

Criar uma Tabela com uma Chave Composta

Nesta etapa, você criará uma tabela chamada enrollments com uma chave composta (composite key) consistindo nas colunas student_id e course_id.

Execute a seguinte instrução SQL no shell do SQLite:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

Este comando cria uma tabela chamada enrollments com três colunas: student_id, course_id e enrollment_date. A cláusula PRIMARY KEY (student_id, course_id) especifica que a chave primária (primary key) para a tabela consiste em ambas as colunas student_id e course_id. Isso significa que a combinação de student_id e course_id deve ser única para cada linha na tabela.

Agora, insira alguns dados na tabela enrollments:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

Isso inserirá três linhas na tabela enrollments.

Em seguida, tente inserir uma linha com o mesmo student_id e course_id que uma linha existente:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

Você deve ver uma mensagem de erro semelhante a esta: Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Isso indica que a restrição de chave composta (composite key constraint) impediu a inserção da linha duplicada.

Para verificar se a tabela foi criada corretamente, você pode usar o comando .tables no shell do SQLite:

.tables

Você deve ver enrollments na saída.

Resumo

Neste laboratório, você aprendeu como definir e implementar restrições (constraints) no SQLite para garantir a integridade dos dados. Você criou tabelas com restrições de chave estrangeira (foreign key constraints), implementou restrições CHECK para validar dados e criou tabelas com chaves compostas (composite keys) para identificar exclusivamente linhas com base em múltiplas colunas. Ao entender e utilizar essas restrições, você pode construir bancos de dados robustos e confiáveis que mantêm a consistência dos dados.