Restrições de banco de dados no Firebird e InterBase
AVISO: Este documento é o capítulo do livro “The InterBase World”, escrito por Alexey Kovyazin e Serg Vostrikov.
Este capítulo é dedicado às restrições dos bancos de dados InterBase e Firebird. As restrições de banco de dados são regras que definem inter-relações entre tabelas e podem verificar e modificar os dados em um banco de dados. Essas regras são implementadas como objetos especiais de banco de dados. A principal vantagem do uso de restrições consiste na capacidade de implementar a verificação de dados e parte da lógica de negócios da aplicação no nível do banco de dados, ou seja, centralizar e simplificar isso, tornando o desenvolvimento de aplicações de banco de dados mais fácil e confiável.
Desenvolvedores iniciantes frequentemente negligenciam o uso de restrições de banco de dados, considerando que elas atrapalham o trabalho criativo. No entanto, na verdade, essa opinião é formada pelo conhecimento insuficiente da teoria e da prática do design de banco de dados.
Ao mesmo tempo, os designers mais experientes se aventuram a recusar o uso de alguns tipos de restrições, fazendo com que suas aplicações ganhem em velocidade. A experiência de designers especialistas permite que eles entendam muito bem o funcionamento do servidor e prevejam com precisão seu comportamento em casos complicados, portanto, é melhor que os programadores iniciantes de InterBase não recorram a ações semelhantes às de colegas experientes.
Neste livro, não abordamos o design de banco de dados; portanto, para mais informações sobre esse assunto, consulte a lista de literatura no final do livro. Aqui, revisaremos apenas todos os tipos de restrições no banco de dados InterBase e consideraremos exemplos de sua aplicação.
Tipos de restrições em um banco de dados
Existem os seguintes tipos de restrições no banco de dados InterBase:
- PRIMARY KEY;
- UNIQUE KEY;
- FOREIGN KEY
- podem ativar gatilhos automáticos - ON UPDATE e ON DELETE;
- CHECK
Nos capítulos anteriores, mencionamos algumas dessas restrições, pois era necessário para a apresentação lógica do material, mas agora consideraremos sua sintaxe, aplicação e implementação em mais detalhes. As restrições de banco de dados são de dois tipos: baseadas em um único campo e baseadas em vários campos da tabela. A sintaxe de ambos os tipos de restrições é fornecida abaixo.
= [CONSTRAINT constraint]
[ …]
= {UNIQUE | PRIMARY KEY
| CHECK ( )
| REFERENCES other_table [( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}
A sintaxe das restrições baseadas em vários campos é a seguinte:
= [CONSTRAINT constraint]
[< tconstraint> …]
= {{PRIMARY KEY | UNIQUE} ( col [, col …])
| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK ( )}
A diferença na sintaxe entre as restrições baseadas em um campo e as baseadas em vários campos é óbvia - na última, podemos especificar vários campos incluídos na restrição. No caso de restrição baseada em um único campo, todas as opções descritas se referem apenas ao campo atual. Certamente, esses dois tipos de restrições têm uma forma diferente de aplicação: as restrições baseadas em um único campo são simplesmente adicionadas à definição do campo necessário, e as restrições baseadas em vários campos são especificadas após a vírgula na definição geral da tabela. Exemplos detalhados são fornecidos nas partes seguintes deste capítulo.
Exemplo de restrição típica
Na verdade, as restrições baseadas em um único campo são um caso especial das restrições baseadas em vários campos.
O exemplo de criação de uma restrição de chave primária usando essas duas abordagens diferentes é fornecido abaixo. Vamos criar uma tabela contendo apenas um campo e definir uma restrição de chave primária para ela.
Aqui está o exemplo da chave primária usando a sintaxe de restrição baseada em um único campo:
CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); Neste exemplo, uma chave primária com o nome pktest é criada para o campo ID_PK. Como resultado, temos uma descrição bastante compacta em uma linha. Podemos usar a sintaxe de restrições baseadas em vários campos para o mesmo fim: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));
Criando restrições
Vamos considerar a criação de restrições em mais detalhes. A primeira na descrição da sintaxe comum de restrições é a opção [CONSTRAINT constraint]. Como você pode ver, essa opção é colocada entre colchetes, ou seja, é opcional.
Usando essa opção, você pode definir o nome da restrição criada, tanto no caso de aplicar a sintaxe de restrições baseadas em um único campo quanto no caso de restrições baseadas em vários campos. Se você não especificou o nome da restrição, o InterBase o gerará automaticamente. No entanto, é melhor definir o nome da restrição criada para melhorar a legibilidade do esquema do banco de dados e simplificar o gerenciamento das restrições posteriormente.
Tendo definido o nome da restrição, seu tipo deve ser definido. Vamos considerar os diferentes tipos de restrições na ordem em que são indicados na descrição da sintaxe comum de restrições.
Chaves primárias e únicas
As chaves primárias são um dos principais tipos de restrições de banco de dados. Elas são aplicadas para a identificação unívoca de registros na tabela. Vamos supor que armazenamos uma lista de pessoas em um banco de dados. É bem possível que haja duas (ou mais) pessoas com o mesmo sobrenome, nome e patronímico. Como podemos distinguir uma pessoa de outra (certamente, a questão é distinguir uma pessoa de outra de acordo com as informações armazenadas em um banco de dados)?
Nesse caso, “pessoa” é representada por um registro na tabela, portanto, podemos fazer uma pergunta mais geral - como podemos distinguir um registro em (qualquer) tabela de outro registro na mesma tabela. Para esse fim, são usadas restrições - chaves primárias. A chave primária representa um ou alguns campos na tabela, cuja combinação é única para cada registro. Não há valores repetidos de chave primária para uma tabela.
As chaves únicas desempenham a mesma função - elas também servem para a identificação unívoca de registros na tabela. A diferença entre chaves primárias e únicas é que pode haver apenas uma chave primária na tabela, e quanto às chaves únicas - algumas. Observa-se que tanto uma chave primária quanto uma chave única podem ser usadas como base de referência para chaves estrangeiras (veja adiante).
A descrição formal das noções de chaves primárias e únicas, bem como outras definições importantes, pode ser encontrada no apêndice “Glossário” no final do livro. A sintaxe de criação de uma chave primária e uma chave única baseada em um único campo é a seguinte:
< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}
Exemplos de chaves primárias e únicas:
CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*uma chave primária*/
uk1 VARCHAR(50) NOT NULL UNIQUE,/*uma chave única*/
uk2 INTEGER NOT NULL UNIQUE /\* mais uma chave única */);
Sintaxe de criação de chaves primárias e únicas baseadas em vários campos:
= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Essa sintaxe permite criar chaves com base em uma combinação de campos. Aqui estão os exemplos de criação de chaves primárias e únicas a partir de vários campos:
CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*chave primária pkt baseada em dois campos*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*chave única ukt1 baseada em dois campos*/
Preste atenção que todos os campos incluídos nas chaves primárias e únicas devem ser declarados como NOT NULL, pois essas chaves não podem ter valor indefinido. Além de criar a restrição de chaves primárias e únicas ao criar a tabela, há a capacidade de adicionar restrições a uma tabela que já existe. Nesse caso, é usada a instrução DDL: ALTER TABLE. A sintaxe de adição das restrições de chave primária ou única à tabela existente é semelhante à descrita acima:
ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Vamos considerar o exemplo de criação de chave primária e única usando ALTER TABLE:
CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));
Então adicionamos as chaves. Primeiro a primária:
ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);
Depois a única: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);
Deve-se observar que apenas o proprietário da tabela ou o administrador do sistema SYSDBA (para mais detalhes sobre os proprietários e o usuário SYSDBA, consulte o capítulo “Segurança no InterBase: usuários, suas funções e direitos” - parte 4) pode realizar a adição (bem como a exclusão) de chaves primárias e únicas à tabela.
Chaves estrangeiras
A próxima restrição frequentemente usada em bancos de dados InterBase é a restrição de chave estrangeira. Esta é uma ferramenta muito poderosa para fornecer integridade referencial em um banco de dados, que permite não apenas supervisionar a presença de referências corretas em um banco de dados, mas também controlar essas referências automaticamente!
O ponto de criar uma chave estrangeira é o seguinte: se duas tabelas servem para armazenar informações inter-relacionadas, é necessário garantir que essa inter-relação esteja sempre correta. Por exemplo, o documento “nota fiscal” contendo o cabeçalho geral (data, número da nota fiscal, etc.) e um conjunto de registros detalhados (descrição das mercadorias, quantidade, etc.).
Para armazenar tal documento, duas tabelas são criadas em um banco de dados - uma para armazenar os cabeçalhos das notas fiscais e a segunda - para armazenar o conteúdo da nota fiscal - registros sobre mercadorias e suas quantidades. Essas tabelas são chamadas de principal e subordinada, ou tabela-mestre e tabela de detalhes.
De acordo com o senso comum, o conteúdo da nota fiscal não pode existir sem a presença de seu cabeçalho. Em outras palavras, não podemos inserir um registro sobre a mercadoria se não criamos o cabeçalho da nota fiscal, e não podemos excluir um registro do cabeçalho se houver registros sobre as mercadorias. Para a realização desse comportamento, a tabela de cabeçalho e a tabela de detalhes são unidas usando uma restrição de chave estrangeira.
Vamos considerar o sentido de definir restrições de chave estrangeira pelo exemplo das tabelas que contêm informações sobre notas fiscais. Para esse fim, criaremos duas tabelas para armazenar a nota fiscal - a tabela TITLE para armazenar o cabeçalho e a tabela INVENTORY para armazenar as informações sobre as mercadorias incluídas na nota fiscal.
CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));
Preste atenção que definimos uma chave primária na tabela de cabeçalho baseada no campo ID_TITLE imediatamente. Os demais campos da tabela TITLE contêm informações triviais sobre o cabeçalho da nota fiscal - data, número, comentário.
Agora vamos definir a tabela para armazenar informações sobre as mercadorias incluídas na nota fiscal:
CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);
Vamos ver quais campos estão incluídos na tabela INVENTORY. Primeiro, é o ID_INVENTORY - uma chave primária desta tabela. Depois vem o campo inteiro FK_TITLE que serve como referência ao identificador de ID_TITLE do cabeçalho na tabela de cabeçalhos de notas fiscais. Em seguida, seguem os campos ProductName, Kolvo e Positio que descrevem a descrição da mercadoria, sua quantidade e uma posição na nota fiscal. O campo FK_TITLE é o mais importante para o nosso exemplo. Se quisermos exibir as informações sobre as mercadorias de uma determinada nota fiscal, devemos usar a seguinte consulta, na qual o parâmetro mas_ID_TITLE define o identificador do cabeçalho:
SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE
Virtualmente, na situação descrita, nada impede de preencher a tabela INVENTORY com registros que se referem a registros inexistentes na tabela TITLE. Além disso, nada interfere em excluir o cabeçalho de uma nota fiscal já existente, por causa do que os registros sobre as mercadorias podem se tornar “sem dono”. O servidor não proibirá a execução de todas essas inserções e exclusões. Assim, o controle sobre a integridade dos dados em um banco de dados é completamente colocado na aplicação cliente. No entanto, você sabe que várias aplicações desenvolvidas, talvez, por diferentes programadores podem trabalhar com um único banco de dados, o que pode levar a diferentes interpretações de dados e erros. Consequentemente, é essencial definir a restrição explícita de que apenas os registros sobre as mercadorias que têm a referência correta ao cabeçalho da nota fiscal podem ser inseridos na tabela INVENTORY. Isso, na verdade, é uma restrição de chave estrangeira que permite inserir apenas os valores que estão na outra tabela nos campos incluídos nas restrições.
Tal restrição pode ser criada usando uma chave estrangeira. Para o exemplo dado, temos que definir restrições de chave estrangeira para o campo FK_TITLE e vinculá-lo à chave primária ID_TITLE na tabela TITLE. Podemos adicionar uma chave estrangeira a uma tabela já existente com o seguinte comando:
ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)
Frequentemente, ao adicionar uma chave estrangeira, o erro aparece - objeto está em uso. O problema é que, para criar uma chave estrangeira, temos que abrir o banco de dados em modo exclusivo - para que não haja outros usuários ao mesmo tempo. Também não devemos nos referir à tabela modificada - isso pode causar o erro “objeto está em uso”.
Aqui, INVENTORY é o nome da tabela para a qual a restrição de chave estrangeira é definida; fktitle1 é o nome da chave estrangeira; FK_TITLE - os campos que compõem a chave estrangeira; TITLE é o nome da tabela que fornece os valores (a base de referência) para a chave estrangeira; ID_TITLE - campos da chave primária ou única na tabela TITLE, que servem como base de referência para a chave estrangeira. A sintaxe completa da restrição de chave estrangeira (com a possibilidade de criar restrições baseadas em vários campos) é fornecida abaixo:
= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Como você pode ver, as definições contêm um grande conjunto de opções. Para começar, vamos considerar uma definição básica de chave estrangeira, que é mais frequentemente usada em bancos de dados reais, e depois analisaremos as opções possíveis.
Uma forma declarativa de restrição de chave estrangeira é mais frequentemente usada quando um conjunto de campos (col [, col …]), que comporão a restrição, é especificado; bem como a other_table que contém uma lista de valores possíveis para a chave estrangeira nos campos [(other_col [, other_col …])].
Aqui está o exemplo de tal definição ao criar a tabela:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);
Observe que nesta definição as palavras-chave FOREIGN KEY são omitidas, bem como o único campo FK_TITLE é implicitamente usado como chave estrangeira. Uma forma mais completa de criar a chave estrangeira simultaneamente com a tabela é fornecida no seguinte exemplo:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);
Usando NULL em campos de uma chave estrangeira
Em campos, com base nos quais uma chave estrangeira é criada, é permitido aplicar campos NULL. Essa possibilidade é adicionada para permitir referências mútuas. Por exemplo, se houver duas tabelas que se referem uma à outra usando chaves estrangeiras. Se não permitirmos a referência vazia (ou seja, NULL) nessas chaves estrangeiras, será impossível adicionar qualquer registro às tabelas vinculadas: para adicionar um registro à primeira tabela, é necessário ter um registro na segunda tabela, e vice-versa.
Usar NULL como referência vazia permite criar referências mútuas de duas tabelas com referência cruzada, e também armazenar estruturas hierárquicas em tabelas relacionais - nesse caso, os nós raiz referem-se a registros “vazios” (ou seja, simplesmente contêm NULL).
Capacidades estendidas de suporte à integridade referencial usando chave estrangeira
Geralmente, a variante declarativa de uma restrição de chave estrangeira é suficiente; o servidor apenas observa que será impossível inserir valores incorretos na tabela com a chave estrangeira ou - ao tentar fazer isso, um erro aparece. Mas o InterBase permite executar um conjunto de operações automáticas ao alterar/excluir uma chave estrangeira. Para esse propósito, o seguinte conjunto de opções de chave estrangeira é usado:
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Essas opções permitem definir diferentes operações ao atualizar ou excluir valores de chave estrangeira.
Por exemplo, podemos definir que, ao excluir uma chave primária na tabela-mestre, todos os registros com a mesma chave estrangeira na tabela subordinada devem ser excluídos. Nesse caso, temos que definir uma chave estrangeira da seguinte maneira:
ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE
Na verdade, para a implementação dessas operações, há um trigger de sistema que executa certas operações. Na tabela 1.2 há uma descrição das operações de diferentes opções (observe que as opções NO ACTION|CASCADE|SET DEFAULT|SET NULL não podem ser usadas em uma mesma sentença ON XXX).
Tabela 1.2
| Evento | Operação | |||
| NO ACTION | CASCADE | SET DEFAULT | SET NULL | |
| ON DELETE | Ao excluir uma chave estrangeira, não fazer nada - é usado por padrão | Ao excluir, excluir todos os registros relacionados da tabela subordinada | Ao alterar, definir um campo de chave estrangeira como valor padrão |
Ao alterar, definir um campo de chave estrangeira como NULL |
| ON UPDATE | Ao alterar, não fazer nada - é usado por padrão | Ao alterar um registro, alterar todos os registros relacionados na tabela subordinada | Ao excluir, definir um campo de chave estrangeira como valor padrão |
Ao excluir, definir um campo de chave estrangeira como NULL |
Se não especificarmos nada ou especificarmos NO ACTION, devemos cuidar da alteração de uma chave estrangeira (no caso de alteração de uma chave primária) por conta própria, e ao excluir a chave primária, devemos excluir os registros da tabela subordinada antecipadamente. Seja muito atento ao usar a opção CASCADE: seu uso descuidado pode levar à exclusão de um grande número de registros relacionados.
Restrição CHECK
Uma das restrições mais úteis em um banco de dados é a restrição check. Sua função é muito simples - verificar o valor inserido na tabela para qualquer condição e, de acordo com a execução dessa condição, inserir os dados ou não. Sua sintaxe é bastante simples:
= [CONSTRAINT constraint] CHECK ( )}
Aqui, constraint é o nome da restrição; é uma condição de busca, na qual o valor inserido/atualizado pode ser usado como parâmetro. Se a condição de busca for cumprida, é permitido inserir/atualizar esse valor; se não for - um erro aparece. O exemplo mais simples de check:
create table checktst( ID integer CHECK(ID>0));
Este check determina se o valor inserido/atualizado do campo ID é maior que zero e, dependendo do resultado, permite inserir/atualizar um novo valor ou informar sobre o erro (veja o capítulo “Capacidades estendidas da linguagem de procedimentos armazenados do InterBase” (parte 1)).
Existem também variantes mais complicadas de checks. Uma sintaxe completa da condição de busca é a seguinte:
= {
{ | ()}
| [NOT] BETWEEN AND
| [NOT] LIKE [ESCAPE ]
| [NOT] IN ( [ , …] | )
| IS [NOT] NULL
| {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} ()
| EXISTS ( )
| SINGULAR ( )
| [NOT] CONTAINING
| [NOT] STARTING [WITH]
| ()
| NOT
| OR
| AND }
Assim, CHECK oferece um grande conjunto de opções para verificar valores inseridos/atualizados. Você deve lembrar das seguintes restrições ao usar CHECK:
- Os dados para CHECK são obtidos apenas do registro atual. Você não deve obter dados para a expressão em CHECK de outros registros da mesma tabela - eles podem ser alterados por outros usuários.
- Um campo pode ter apenas uma restrição CHECK.
- Se para a definição de campo for usado um domínio que tenha restrição de domínio CHECK, ele não pode ser redefinido no nível de um campo concreto na tabela. Deve-se dizer que CHECK é implementado por triggers de sistema, portanto, devemos ser mais cautelosos ao usar condições muito longas, que podem desacelerar fortemente os processos de inserção e atualização de registros.
Excluindo restrições
Muitas vezes, excluímos diferentes restrições pelos mais diversos motivos. Para excluir uma restrição, devemos usar a instrução ALTER TABLE da seguinte forma: ALTER TABLE tablename DROP CONSTRAINT constraintname
constraintname é o nome da restrição que deve ser excluída. Se um nome específico foi especificado ao criar a restrição, devemos usá-lo; mas se não, temos que abrir qualquer ferramenta de administração do InterBase, procurar todas as restrições relacionadas a ela e descobrir qual nome de sistema o InterBase gerou para a restrição necessária.
Deve-se notar que apenas o proprietário da tabela ou o administrador do sistema SYSDBA pode excluir as restrições.