Tabelas. Chaves primárias e geradores
NOTICE: Este documento é o capítulo do livro “The InterBase World”, escrito por Alexey Kovyazin e Serg Vostrikov.
O InterBase é um SGBD relacional. Além disso, isso significa que todos os dados no InterBase são armazenados como tabelas. A tabela, como é vista do ponto de vista do SQL, é muito semelhante à tabela comum, que pode ser desenhada à mão em uma folha de papel ou criada em um programa como o Microsoft Excel. As tabelas no InterBase têm colunas e linhas onde os dados são colocados. A tabela necessariamente tem um nome, único dentro de um banco de dados. As tabelas são o principal armazenamento de informações em um banco de dados e, consequentemente, você deve ter muito cuidado ao criar tabelas.
Existem regras que descrevem como criar tabelas no banco de dados relacional, refletindo os dados do mundo real e, ao mesmo tempo, permitindo organizar o armazenamento eficiente das informações em um banco de dados. O processo de aplicação dessas regras para projetar um banco de dados “correto” é chamado de normalização. Colocamos a palavra “correto” entre aspas de propósito, porque “banco de dados normalizado” e “banco de dados otimizado” não são sinônimos. Você não precisa seguir as regras de normalização de forma absoluta - sempre aplique a correção para a especificação de um determinado problema.
A normalização de tabelas em um banco de dados é detalhada no livro [14. e, portanto, não tentaremos abraçar o inabrangível e retornaremos ao nosso tema de discussão - as tabelas do InterBase. Vamos considerar a sintaxe da sentença DDL (DDL - Data Definition Language, para mais detalhes veja o glossário) que permite criar tabelas:
CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);
Aqui, table é o nome da tabela criada, - a descrição das colunas (às vezes diremos - campos) da tabela criada. A opção table [EXTERNAL [FILE] “”] significa que a chamada tabela externa, que não é armazenada em um arquivo de banco de dados compartilhado, mas em um arquivo separado com um nome , será criada. Como você pode ver, tudo é simples - definimos um nome de tabela e as colunas que ela contém. Agora vamos considerar em detalhes como definir colunas. A sintaxe para criar uma coluna é descrita pela seguinte sentença DDL:
= col { datatype | COMPUTED [BY] (< expr>)} | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[COLLATE collation]
Esta é uma definição bastante grande, porém, na definição de uma coluna, apenas uma pequena parte das sentenças fornecidas é obrigatória. Cada coluna na tabela deve ter um nome, único dentro da tabela, bem como um tipo de dados definido pela sentença datatype, ou uma expressão para calcular o valor da coluna (para colunas calculadas), ou o domínio (veja abaixo), definido domain. Os tipos de dados foram considerados no capítulo “Tipos de dados”; portanto, você pode facilmente entender como a expressão SQL para criar uma tabela é formada.
Vamos nos conectar ao nosso banco de dados FIRSTBASE.gdb criado anteriormente no capítulo “Criar um banco de dados”, e tentaremos trabalhar com tabelas na prática. Quando se trata de criar, excluir e atualizar tabelas, qualquer uma das ferramentas administrativas do InterBase - das listadas no aplicativo “Ferramentas de administrador e desenvolvedor do InterBase”, bem como o utilitário padrão isql.exe de um conjunto de fornecimento de qualquer clone do InterBase, será adequada.
Aqui está um exemplo de uma tabela simples chamada TABLE_EXAMPLE contendo 3 campos de vários tipos:
CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Esta tabela ilustra o caso mais frequente no processo de desenvolvimento de um banco de dados. No entanto, existem também outros métodos de definição de campos. Por exemplo, podemos definir o tipo de um campo usando domínios. O domínio é um tipo definido pelo usuário para a conveniência de aplicar certas combinações de parâmetros de tipo. Por exemplo, é possível definir o domínio D_ID para especificar campos de identificadores. Tendo definido o domínio, podemos usá-lo para definir o tipo de um campo:
CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
O campo ID terá o tipo definido pelo domínio D_ID. Assim, tendo definido o tipo do campo no domínio, verificações e restrições necessárias, podemos aplicar este domínio muitas vezes para criar campos da mesma função. Por exemplo, monetário, sem a tarefa tediosa e com o perigo de errar ao copiar definições de tipos de variáveis. A terceira maneira de definir uma coluna na tabela é defini-la como calculada (COMPUTED BY) e especificar uma condição segundo a qual seu valor será calculado. Por exemplo, podemos querer ter uma coluna que calcule 10% do valor do campo PRICE_1 em nossa tabela. Nesse caso, o seguinte comando deve ser escrito:
CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));
Mas não pense que, assim que inserirmos os dados no campo PRICE_1, no campo PRICE_10 haverá uma décima parte do valor desse campo. Não, o processo aqui é mais complicado. Na verdade, obteremos a décima parte necessária apenas ao nos referirmos ao campo PRICE_10, por exemplo, ao executar uma consulta SELECT nesta tabela. Ou seja, nenhum dado é armazenado em um campo calculado, e a busca do cálculo da expressão conectada a um campo é realizada, e o resultado é produzido como resposta à consulta.
Então, consideramos 3 maneiras principais de especificar campos na tabela. Agora vamos considerar em detalhes as opções que podem ser definidas ao criar uma coluna. A opção [DEFAULT {literal | NULL | USER}] - permite definir um valor padrão para a coluna. É muito conveniente para o preenchimento automático de dados. Existem 3 maneiras de definir um padrão. A primeira é designada como literal e permite definir padrões como constantes de texto, números ou datas. Por exemplo, podemos gerar as seguintes expressões para criar uma coluna com padrões de texto: NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’
Assim, todos os campos inseridos na tabela assumirão os padrões, ou seja, se outro valor não tiver sido definido para o campo NAME, a string ‘Vasily Stanislavovich’ aparecerá. A segunda maneira de definir um padrão é especificar DEFAULT NULL na definição de uma coluna. E em registros criados novamente, o valor desta coluna será NULL, se outro valor, certamente, não tiver sido definido explicitamente. Um exemplo:
PRICE_1 DOUBLE PRECISION DEFAULT NULL
A terceira maneira de definir um padrão é especificar DEFAULT USER na definição de uma coluna. Assim, em registros criados novamente, este campo conterá o nome do usuário atual, ou seja, o usuário que fez a conexão com o InterBase e executou esta inserção (para mais detalhes sobre usuários, veja o capítulo “Segurança no InterBase: usuários, suas funções e direitos” (parte 4)). Para alguns campos, é essencial que o campo tenha um valor não vazio. Por exemplo, um campo que não pode ser vazio de acordo com a especificação do problema. Para definir uma restrição no nível do banco de dados de que um campo deve ter um valor definido, é necessário fazer a seguinte adição à descrição da coluna:
NAME VARCHAR(80) NOT NULL
Assim, haverá um campo no qual nulos não podem ser armazenados. Geralmente, a restrição NOT NULL é combinada com a opção DEFAULT, que definitivamente atribui um valor correto a este campo. Mas frequentemente a restrição NOT NULL não é suficiente. Por exemplo, no caso de armazenar preços em um banco de dados, é bastante claro que eles não podem assumir valores negativos (embora seria ótimo se fôssemos pagos a mais ao comprar mercadorias). Para fazer o servidor verificar os valores dos preços inseridos em um banco de dados sob uma condição de positividade, é necessário definir uma coluna da seguinte maneira:
PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)
Os valores inseridos na coluna PRICE_1 serão verificados sob uma condição de positividade. Deve-se notar que diferentes opções consistentes podem ser combinadas e, por exemplo, podemos definir um valor não vazio e verificar a positividade:
PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)
Ao criar as colunas, algumas opções não podem ser combinadas, por exemplo, é impossível definir NULL como padrão e simultaneamente a restrição de valor não vazio. Deve-se destacar que as verificações podem realizar um conjunto de funções úteis no gerenciamento de dados em um banco de dados. Consideraremos seu uso em detalhes no capítulo “Restrições de banco de dados”.
Então, consideramos as maneiras de criar tabelas e campos com diferentes opções. No entanto, há casos em que precisamos alterar uma tabela que já existe. Certamente, podemos recriar a tabela inteira. Primeiro, devemos executar o comando de exclusão da tabela e depois criá-la novamente. Por exemplo:
DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);
Mas essa maneira de alterar tabelas tem desvantagens significativas. Ao excluir uma tabela usando o comando DROP, todos os dados que a tabela contém são excluídos e, para não perdê-los, é necessário copiá-los para tabelas temporárias. Isso é bastante trabalhoso. Portanto, existe o comando ALTER TABLE para alteração fácil da estrutura das tabelas, que permite adicionar novos campos, excluir existentes, bem como adicionar/excluir restrições de integridade referencial.
Por exemplo, queremos adicionar mais uma coluna à tabela destinada a armazenar os dados sobre o patronímico de uma pessoa:
ALTER TABLE Table_example ADD Patronimic VARCHAR(80);
Após a execução deste comando, nossa tabela Table_example terá uma nova coluna com o nome Patronimic e tipo VARCHAR (80). Se quisermos excluir uma coluna com o nome NAME da tabela, devemos executar o seguinte:
ALTER TABLE Table_example DROP Name;
Você pode ver a sintaxe completa da declaração ALTER TABLE em [1.. É um comando muito útil, e o usaremos com frequência.
E o que fazer, você perguntará, se for necessário modificar uma coluna? Por exemplo, decidimos que, para armazenar nomes, é melhor usar o campo HUMAN_NAME do que NAME. Nesse caso, podemos aplicar ALTER TABLE
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Se decidirmos alterar o tipo de um campo, por exemplo, aumentar o número de caracteres armazenados em um campo, teremos que alterar o domínio deste campo usando a declaração ALTER DOMAIN (veja o capítulo “Tipos de dados” acima).
Então, consideramos a criação e modificação de tabelas no InterBase. Agora é hora de aprofundar um pouco na teoria dos bancos de dados. O InterBase, como já foi dito, é um banco de dados relacional. Além disso, isso significa que cada registro na tabela deve ter uma característica segundo a qual um registro pode ser distinguido de outro. O mecanismo especial de chaves únicas serve a esse propósito.
Chaves primárias em tabelas
Certamente, podemos criar uma tabela que não contenha nenhuma chave. Não somos proibidos de fazer isso. Mas, como foi dito anteriormente, a criação de um banco de dados eficiente é impossível sem observar as regras de normalização. A presença de chaves é o elemento mais importante da normalização. Portanto, embora não tenhamos como objetivo considerar a teoria e a normalização de bancos de dados, devemos introduzir uma definição de chaves e revisar sua função no InterBase. Iremos passo a passo e começaremos com o tipo mais comum de chave - a chave primária.
Então, o que é uma chave primária? É um ou mais campos na tabela que identificam exclusivamente registros dentro desta tabela. Parece difícil, mas na verdade tudo é muito simples. Imagine uma tabela comum, por exemplo, a folha de contabilidade. Qual é a primeira coluna? Isso mesmo, o número de série - 1, 2, 3 … Este número denota uma linha única dentro da tabela, e é suficiente saber este número para encontrar uma string nesta tabela. Neste exemplo, será uma chave primária. A esmagadora maioria das tabelas em um banco de dados relacional necessariamente tem uma chave primária (PK - abreviação de Primary key). A diretriz comum ao criar tabelas é criar uma chave primária. Uma chave primária pode ser criada ao criar a tabela ou posteriormente. Vamos supor que, no momento da criação da tabela, decidimos que o campo ID será nossa chave primária. Então, podemos adicionar uma chave primária da seguinte maneira:
CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));
O que precisa ser feito para criar uma chave primária para a tabela table_example? Vamos pesquisar o que variou na definição da tabela? Primeiro, a coluna ID recebeu uma definição adicional NOT NULL. Isso é importante, porque a chave primária deve ser única e não pode conter valores indefinidos. E NULL, como você sabe, é um valor indefinido. Assim, todos os campos incluídos em uma chave primária devem ter a restrição NOT NULL. Para completar a criação de uma chave primária, o seguinte deve ser escrito no final da tabela: CONSTRAINT ()
Você pode encontrar a sintaxe completa das restrições no capítulo “Restrições de banco de dados” þ. 1, e para o nosso exemplo de chave primária, ficará assim:
CONSTRAINT pkTable PRIMARY KEY (ID)
Aqui, pkTable é o nome da chave primária, e ID são as colunas que ela contém. Essa forma de definir chaves primárias para tabelas é conveniente na criação em massa de tabelas (por exemplo, ao construir o protótipo de um banco de dados com base em scripts obtidos de diferentes recursos CASE). Mas o que fazer se precisarmos adicionar/remover uma chave primária de uma tabela que já existe e está preenchida com dados? Para isso, deve-se aplicar mais uma extensão do comando - ALTER TABLE. Um exemplo de adição de uma chave primária à nossa tabela:
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);
Assim, a tabela Table_example terá exatamente a mesma chave primária do exemplo anterior, quando foi criada junto com a tabela. Para excluir uma chave primária, o seguinte comando deve ser inserido:
ALTER TABLE Table_example DROP CONSTRAINT pkTable;
Assim, a chave com o nome pkTable será removida do banco de dados.
Geradores - melhores amigos das chaves primárias
Temos que dizer algumas palavras sobre a implementação de uma chave primária. Como ela serve para garantir a unicidade, nenhum dois registros em uma mesma tabela podem ter os mesmos valores dessa chave. Ou seja, para atender à condição, ao inserir um novo registro na tabela, o InterBase tem que verificar todos os registros da tabela e descobrir se a tabela contém tais valores ou não. Para uma busca rápida, o InterBase tem um mecanismo de índices - objetos especiais do InterBase, que permitem encontrar um registro na tabela muito rapidamente. Portanto, ao criar e excluir a chave primária, o índice é criado ou excluído para esse campo (ou campos) que está incluído na chave primária.
Como foi dito anteriormente, a chave primária pode conter vários campos. Assim, podemos notar a unicidade de uma combinação de valores desses campos. Por exemplo, se definirmos uma chave para os campos ID e NAME, o servidor controlará que não haja combinações idênticas desses campos na tabela. Ou seja, combinações dos campos ID e 1 e “Ivanov”, 2 e “Ivanov” serão corretas, pois diferem nos valores do campo ID.
Assim, a chave primária pode incluir vários campos de qualquer tipo. No entanto, na prática, o tipo mais comum de chave é o contador - um campo inteiro, que contém valores crescentes. Por que isso? É o reflexo de uma antiga disputa entre chaves naturais e substitutas. O conceito de chaves naturais diz que, como chave, devemos tentar usar os valores realmente existentes no domínio de dados que um banco de dados reflete. Por exemplo, se desenvolvermos um sistema de registro de pessoas para um cartório de passaportes, de acordo com esse conceito, uma combinação de número e série do passaporte deve ser tomada como chave primária. Realmente, toda pessoa deve ter uma combinação única de número e série do passaporte. No entanto, o que fazer com o fato de que uma pessoa pode trocar de passaporte durante a vida (em conexão com atingir uma idade definida, casamento, etc.)? Nesse caso, teremos que alterar o número e a série do passaporte definidos em correspondência com a pessoa concreta, ou seja, na prática, alterar nossa chave primária. Isso é indesejável do ponto de vista do desenvolvimento de aplicações de banco de dados: levando em conta um sistema ramificado de comunicação entre tabelas (o próximo capítulo é dedicado a isso), o desenvolvedor terá que se esforçar muito para controlar essa situação.
Portanto, uma chave substituta é usada na maioria dos casos. Substituta - significa artificial, ou seja, não existente no domínio de dados que nosso banco de dados descreve, e criada artificialmente - para a conveniência do desenvolvimento de aplicações de banco de dados. Como foi dito, geralmente um contador é a chave primária. Alguns SGBDs, como Paradox e MS SQL, têm um tipo especial - o contador (auto incremento). Ao adicionar um novo registro à tabela, o valor do campo aumenta automaticamente com esse tipo pelo valor de um incremento - geralmente por unidades. No InterBase, não há campo do tipo contador, no entanto, tal comportamento pode ser realizado. Para criar o campo, que seria preenchido automaticamente ao adicionar um registro à tabela, é usada uma coleção de recursos: o primeiro deles é o gerador.
O que é um gerador? Falando de forma simples, gerador é um contador nomeado. Dentro de um banco de dados, podemos criar um contador, dar um nome único a ele dentro dessa base e controlar os valores desse contador. Isso será um gerador. Aqui está um exemplo de instruções DDL que explicarão isso para você:
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
Na primeira linha deste exemplo, o gerador com nome g1 é criado, e na segunda linha, o valor 2445 é atribuído a esse gerador. Agora há uma pergunta: como usar o gerador obtido? Há uma função embutida GEN_ID no InterBase para obter e alterar os valores dos geradores. Essa função recebe como parâmetros o nome do gerador e o valor do incremento, que deve ser aplicado ao gerador dado, e retorna o valor inteiro correspondente ao valor do gerador, recebido como resultado da adição do incremento a ele. Aqui está um exemplo de chamada da função GEN_ID em um trigger ou procedimento armazenado:
Current_value = GEN_ID (g1, 1)
Se quisermos receber o valor do gerador, podemos usar a seguinte consulta:
SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE
Como a tabela RDB $ Database sempre contém apenas um registro, receberemos um valor do gerador g1 como resultado da consulta dada.
Aqui, current_value é uma variável (nos próximos capítulos você encontrará informações sobre como usar variáveis no InterBase), g1 - um gerador, 1 - incremento. Neste exemplo, o valor do gerador g1 será atribuído à variável current_value após adicionar o incremento 1 a ele, ou seja, o próximo valor do gerador. Preste atenção que o incremento pode não ser igual a 1! Além disso, pode até ser negativo: Current_value = GEN_ID (g1, -23)
Como resultado da execução dessa função, o valor atual do gerador g1 será menos 23. Como você pode ver, a gama de aplicações possíveis dos geradores é bastante ampla - pode ser usada não apenas para obter valores de chaves primárias, mas também para monitorar mudanças globais em um banco de dados.
Pessoas familiarizadas com bancos de dados podem fazer uma pergunta: “o que acontecerá se simultaneamente alguns clientes tentarem colocar dados na mesma tabela e simultaneamente ‘puxarem’ os geradores? Eles receberão os mesmos ou diferentes valores do gerador?”. Eles receberão inequivocamente valores DIFERENTES do gerador. Por mais “simultânea” que seja a tentativa de receber o valor do gerador, todos que solicitarem receberão o valor único. Isso é garantido pela “construção” dos geradores: eles funcionam no nível mais baixo do servidor e nenhum processo de gravação e inserção os influencia - costuma-se dizer que os geradores funcionam “fora do contexto das transações”. Se você quiser saber sobre transações, leia o capítulo “Transações. Parâmetros de transações” (parte 1); como os geradores são organizados - “Estrutura do banco de dados InterBase” (parte 4). Bem, em nome dos geradores, temos um mecanismo confiável para criar chaves primárias únicas. No entanto, podemos usar esse mecanismo? Como colocar o valor recebido do gerador em um campo da chave primária?
Para esse fim, há duas maneiras - inserir uma chave primária em nome de um cliente e em nome de um servidor. Para dominar a primeira maneira, devemos consultar o capítulo “Uso dos principais componentes do FIBPlus” e para entender a segunda - o capítulo “Triggers” (parte 1). Aqui, revisaremos brevemente o ponto principal de ambas as maneiras.
No caso de criar uma chave primária em nome de um cliente, o seguinte acontece. Quando o registro que será inserido em um banco de dados é gerado, a chamada da função GEN_ID (, 1) é executada e o valor recebido é substituído por esse registro. Então, a inserção na tabela ocorre, e temos a garantia de receber uma chave primária única.
A segunda maneira - criar uma chave primária em nome de um servidor - em geral elimina qualquer preocupação da parte do cliente sobre qual valor da chave primária será. Nesse caso, ao inserir o registro, o trigger funciona - um objeto especial do banco de dados, que pode realizar quaisquer operações ao inserir/excluir/atualizar os registros nas tabelas. E nesse trigger, as seguintes operações são cumpridas: chamada da função GEN_ID, recebimento do valor necessário do gerador e sua inserção na tabela. A vantagem da segunda maneira é que, ao desenvolver uma aplicação cliente, não há necessidade de se preocupar com a criação da chave primária, a única coisa que você tem que fazer é escrever um trigger necessário uma vez. Mas a desvantagem é que não podemos receber o valor da chave gerada na aplicação logo após a inserção! Se usarmos a primeira maneira, podemos receber o valor da chave primária, embora devamos nos preocupar com sua criação a cada inserção. É difícil dizer com certeza qual maneira é melhor, tudo depende de um problema específico. Mais adiante neste livro, consideraremos variantes possíveis de resolver as questões sobre o trabalho com uma chave primária.
Conclusão
Então, neste capítulo, revisamos como criar e atualizar tabelas no InterBase, bem como lidar com chaves primárias. Assim, consideramos os principais objetos no InterBase que podem ser condicionalmente chamados de estáticos, porque eles apenas armazenam as informações e não realizam sua conversão. A seguir, falaremos sobre as maneiras de controle de informações e conversão de informações dentro de um banco de dados.
|