Baixe o app para aproveitar ainda mais
Prévia do material em texto
Capítulo 11 Criando, eliminando e modificando tabelas Bancos de dados relacionais baseiam-se em tabelas. Cada tabela tem suas características pró- prias, como as colunas que a compõem, os identificadores e as restrições de integridade que devem ser respeitadas. Tabelas têm elos lógicos entre si que possibilitam a criação de comple- xos modelos, sobre os quais os mais variados tipos de aplicações podem ser construídas. Tudo isso é parte do arcabouço descritivo de um banco de dados. Através da estrutura de tabe- las e das restrições impostas, determina-se o quê esse banco de dados pode aceitar. Pode-se pensar nisso como uma definição lógica, onde ficam estabelecidas as “regras do jogo”. Tabelas têm instâncias, constituídas pelos registros nelas armazenados. Usuários alimentam suas bases de dados manipulando esses registros, seja incluindo-os, removendo-os ou alterando seus valores. Essas operações ocorrem, sempre, dentro das regras definidas. Eventualmente essas regras podem mudar, visto que um banco de dados pode ter sua definição lógica revista. Este capítulo trata da definição das tabelas e das regras de uma base de dados. Os comandos aqui apresentados servem para criar, alterar e eliminar tabelas, além de definir os relacionamen- tos entre elas. A manipulação das instâncias das tabelas, via operações sobre registros, é vista no capítulo 12. As definições de um banco de dados usualmente fazem parte das tarefas do administrador (Da- taBase Administrator) e não estão acessíveis ao universo de usuários por vários motivos, segu- rança entre eles. Esse trabalho, normalmente, é efetuado através de interfaces especializadas, do tipo point & click, ficando bastante facilitado em comparação com a codificação textual. Mes- mo assim, conhecer os comandos SQL é importante para administradores e usuários avançados. 11.1 CRIANDO TABELAS Para criar uma tabela num banco de dados relacional, é preciso definir e descrever seus diversos elementos componentes. Os principais aspectos que caracterizam uma tabela relacional são listados a seguir. • Nome da tabela cada tabela tem um nome que a identifica; • Atributos das tabelas, sendo que cada atributo pode ter Tipo e tamanho, que regem os valores que o mesmo pode assumir; Valor default, que é o valor padrão do atributo quando um novo registro é criado; Permissão para valores nulos, sobre a aceitação de valores nulos ou não; Autonumeração, que possibilita a geração automática de valores; • Chave primária uma tabela pode ter, opcionalmente, um conjunto de um ou mais atributos que constituem um identificador único; • Restrições de valores 210 Capítulo 11: Criando, eliminando e modificando tabelas uma tabela pode ter, opcionalmente, uma ou mais regras de aceitação de registros. Essas regras são estabelecidas sobre os valores dos seus atributos e são verificadas sempre que um registro é incluído ou alterado. • Chaves candidatas um conjunto de um ou mais atributos pode constituir um identificador único para a tabela. Uma tabela pode ter zero ou mais chaves candidatas; • Chaves estrangeiras um conjunto de um ou mais atributos pode constituir uma chave estrangeira. Uma tabela pode ter zero ou mais chaves estrangeiras. ∴ Na sua forma mais simples, uma tabela tem um nome e uma lista de atributos, cada um com seu tipo. Assim, um comando que crie uma tabela deve conter, no mínimo, esses elementos, como nos trechos abaixo. create table curso ( codcurso char (3), curso varchar (40) ) create table aluno create table aluno ( ( matricula int, matricula int, nome varchar (40), nome varchar (40), sexo char (1), sexo char (1), codcurso char (3), codcurso char (3), nascimento datetime nascimento date ) ) create table professor ( codprofessor int, professor varchar (40) ) create table disciplina ( coddisciplina int, disciplina varchar (40), codprofessor int, chst float, chsp float ) create table inscricao ( matricula int, coddisciplina int, nota float ) Os comandos create table, acima, criam versões simplificadas das tabelas do nosso banco e- xemplo1. A tabela CURSO tem dois atributos, dos tipos char e varchar, o primeiro de 3 caracteres e o segundo com tamanho máximo de 40 caracteres. A segunda tabela, ALUNO, possui cinco atri- butos, sendo o último do tipo datetime. É claro que, normalmente, as tabelas em bancos de dados são um pouco mais complicadas do que isso; ainda não há chaves primárias, chaves es- trangeiras, restrições de integridade e outras características pertinentes ao nosso modelo. 1 Nossa escolha limitou-se aos tipos de dados presentes nas quatro implementações SQL aqui utilizadas, com exceção para o tipo datetime que não está disponível no Oracle. Sem esta restrição, é possível fazer melhores escolhas para cada implementação específica. Declarando as características de uma tabela 211 11.2 DECLARANDO AS CARACTERÍSTICAS DE UMA TABELA Os gerenciadores de bancos de dados requerem o estabelecimento de diversas características para as tabelas. Algumas delas são de ordem lógica, tal como o número de colunas e seus tipos, a chave primária ou restrições de valores. Outras características, entretanto, são de ordem ope- racional e, portanto, peculiares de cada implementação. Por exemplo, pode-se designar o espa- ço de armazenagem em disco para a tabela, ou estabelecer outros parâmetros específicos que extrapolam os aspectos clássicos do modelo relacional. Esta seção aborda as características de ordem geral de uma tabela relacional. Aspectos específi- cos serão abordados separadamente, nas seções dedicadas às implementações SQL. ESCOLHA DOS TIPOS A escolha de tipos adequados para os atributos, de acordo com os possíveis valores que os mesmos venham a assumir, é uma etapa extremamente importante no projeto de bancos de da- dos. Escolhas precipitadas e não plenamente fundamentadas podem resultar em custosos e de- morados processos de reconfiguração mais adiante, que devem ser evitados. Os principais fatores que influenciam a escolha do tipo de um atributo são: • os possíveis valores que esse atributo pode assumir; • as prováveis operações a serem efetuadas com esse atributo; • o espaço de armazenagem que cada tipo demanda. O capítulo 7 apresenta uma detalhada exposição dos tipos disponíveis nos diversos gerenciado- res de bases de dados analisadas neste livro e descreve os aspectos técnicos envolvidos numa boa escolha. CONSTRAINTS O termo constraint refere-se, genericamente, às restrições impostas sobre os valores que os atri- butos de uma tabela podem assumir. Chaves primárias e secundárias, chaves estrangeiras e restrições de valores podem ser imaginadas como restrições que a instância de um banco de dados deve observar. Normalmente, cada constraint é identificada por um nome único, pois assim é possível, numa eventual violação das restrições lógicas do banco, identificar a regra de integridade que está sendo desrespeitada. Por exemplo, exigir que uma nota esteja entre 0 e 10 é uma restrição de valor. Outro exemplo, é determinar que o código do curso de um aluno esteja entre os códigos de curso que aparecem na tabela CURSO (restrição de integridade referencial). As seções seguintes apresentam constraints que manifestam-se de várias formas por ocasião da definição de tabelas relacionais. VALORES NULOS Um atributo pode aceitar valores nulos ou não. Este aspecto está relacionado à qualidade de preenchimento em bancos de dados. No exemplo, há alguns atributos que não permitem nulos, tais como o nome de um curso: é incorreto cadastrar um curso sem que seu nome esteja preen- chido. Por outro lado, a ausência de informações sobre a data de nascimento de um aluno não é impeditivo de sua inclusão, podendo-se aceitar um registro onde tal coluna ainda não estejapreenchida. create table curso ( codcurso char (3) not null, curso varchar (40) not null ) 212 Capítulo 11: Criando, eliminando e modificando tabelas create table aluno create table aluno ( ( matricula int not null, matricula int not null, nome varchar (40) not null, nome varchar (40) not null, sexo char (1), sexo char (1), codcurso char (3) not null, codcurso char (3) not null, nascimento datetime nascimento date ) ) create table professor ( codprofessor int not null, professor varchar (40) not null ) create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null codprofessor int, chst float not null, chsp float not null ) create table inscricao ( matricula int not null, coddisciplina int not null, nota float null ) Note que todos os atributos declarados como not null devem ser obrigatoriamente preenchidos com valores válidos nos domínios de seus tipos. A título de exemplo, o atributo nota, que aceita nulos, foi declarado explicitamente com a palavra-chave null. Normalmente, se nada for dito em contrário, um atributo aceita nulos. CHAVES PRIMÁRIAS (PRIMARY KEYS) A chave primária de uma tabela é composta por um ou mais atributos que, conjuntamente, iden- tificam univocamente um registro; não há dois registros, na instância de uma tabela, com os mesmos valores para os atributos que formam a chave primária. A chave primária é o identifi- cador principal, e seus atributos componentes não podem conter valores nulos. Sendo um tipo de constraint, chaves primárias podem receber um nome. create table curso ( codcurso char (3) not null, curso varchar (40) not null, constraint pk_curso primary key (codcurso) ) create table aluno create table aluno ( ( matricula int not null, matricula int not null, nome varchar (40) not null, nome varchar (40) not null, sexo char (1), sexo char (1), codcurso char (3) not null, codcurso char (3) not null, nascimento datetime nascimento date constraint pk_aluno primary key (matricula) constraint pk_aluno primary key (matricula) ) ) create table professor ( codprofessor int not null, professor varchar (40) not null, constraint pk_professor primary key (codprofessor) ) create table disciplina ( coddisciplina int not null, Declarando as características de uma tabela 213 disciplina varchar (40) not null, codprofessor int, chst float not null, chsp float not null, constraint pk_disciplina primary key (coddisciplina) ) create table inscricao ( matricula int not null, coddisciplina int not null, nota float null, constraint pk_inscricao primary key (matricula, coddisciplina) ) A chave primária da tabela INSCRIÇÃO é composta por dois atributos, matrícula e coddisciplina. Isto significa que pode haver mais de uma inscrição para uma mesma matrícula e mais de uma inscrição para a mesma disciplina, mas não pode haver mais de uma inscrição para a mesma combinação dos atributos matrícula e coddisciplina. Em algumas implementações, quando a chave primária compreende apenas um atributo, é possível especificar esta propriedade na pró- pria declaração do atributo, conforme é apresentado nas seções específicas, mais adiante neste capítulo. Neste caso, a definição da tabela CURSO, por exemplo, poderia ter sido escrita como create table curso ( codcurso char (3) not null primary key, curso varchar (40) not null ) Note que não é obrigatório que uma tabela tenha uma chave primária. Em certos casos, é possível construir tabelas sem identificadores, ou mesmo tabelas que possuam identifica- dores e, ainda assim, não tenham uma chave primária definida. INTEGRIDADE REFERENCIAL (CHAVES ESTRANGEIRAS) O mecanismo de integridade referencial é um importante elemento dos bancos de dados relacio- nais, pois garante que referências intertabelas permaneçam íntegras ao longo da existência do banco dados. Essas referências são chamadas de chaves estrangeiras (foreign keys) e podem ser definidas sobre atributos singelos ou conjuntos de atributos. No nosso exemplo, há uma chave estrangeira definida na tabela ALUNO referenciando a tabela CURSO, além de outras três, entre as tabelas DISCIPLINA e PROFESSOR, INSCRIÇÃO E DISCIPLINA e INSCRIÇÃO e ALUNO, como mostra o esquema a seguir (veja mais detalhes sobre este esquema relacional no capítulo 1). Disciplina Curso Inscricao Professor 1 1 1 1 0:1 0:n 0:n 0:n Aluno Matricula Nome Sexo CodCurso Nascimento CodDisciplina Disciplina CodProfessor CHST CHSP CodCurso Curso Matricula CodDisciplina Nota CodProfessor Nome Chaves estrangeiras, sendo um tipo de constraint, podem ser nomeadas. A descrição do esque- ma fica sendo como abaixo. create table aluno ( matricula int not null, nome varchar (40) not null, sexo char (1), codcurso char (3) not null, nascimento datetime, constraint pk_aluno primary key (matricula), constraint fk_aluno_curso foreign key (codcurso) references curso 214 Capítulo 11: Criando, eliminando e modificando tabelas ) create table aluno ( matricula int not null, nome varchar (40) not null, sexo char (1), codcurso char (3) not null, nascimento date, constraint pk_aluno primary key (matricula), constraint fk_aluno_curso foreign key (codcurso) references curso ) create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float not null, chsp float not null, constraint pk_disciplina primary key (coddisciplina), constraint fk_disciplina_professor foreign key (codprofessor) references professor ) create table inscricao ( matricula int not null, coddisciplina int not null, nota float null, constraint pk_inscricao primary key (matricula, coddisciplina), constraint fk_inscricao_aluno foreign key (matricula) references aluno (matricula), constraint fk_inscricao_disciplina foreign key (coddisciplina) references disciplina (coddisciplina) ) Observe, no primeiro comando, que quando a chave estrangeira referencia a chave primária de outra tabela não é preciso listar os atributos que a compõem. Nem sempre a chave primária é obrigatoriamente referenciada numa chave estrangeira. Qualquer conjunto de atributos que configurem uma chave candidata (vide seção a respeito mais adiante) pode ser referenciado numa chave estrangeira. ∴ Um caso interessante surge no exemplo na definição da tabela DISCIPLINA. Note que o atributo codprofessor é uma chave estrangeira em relação à tabela PROFESSOR. Contudo, esse atributo admite o valor nulo, o que significa que pode haver uma disciplina com o professor ainda inde- finido, isto é, com o atributo codprofessor não preenchido. Porém, se o atributo estiver pren- chido deverá necessariamente fazer referência a algum professor que exista. ∴ Algumas implementações SQL permitem que, por ocasião da definição das chaves estrangeiras, se possa definir a reação do sistema gerenciador quando operações que ocasionam violações da integridade referencial são executadas sobre as tabelas. Para ilustrar este ponto, repetimos abai- xo as instâncias das tabelas DISCIPLINA e INSCRIÇÃO. DISCIPLINA CodDisciplina Disciplina CodProfessor CHST CHSP 112 Dir. Constitucional 3 4 0 114 Direito Civil 10 4 2 117 Estatística 15 2 2 210 Compiladores 2 2 4 211 Bancos de Dados 17 3 3 316 Sociologia 18 3 1 317 Português 12 4 0 INSCRIÇÃO Matricula Nota CodDisciplina 1001 NULL 112 1001 8,0 317 1002 9,5 210 1005 3,0 316 1005 4,2 117 1007 NULL 112 1007 7,0 114 1010 5,5 317 1010 10,0 316 Declarando as características de uma tabela 215 Na definição da tabela INSCRIÇÃO, fica estabelecido que o atributo coddisciplina constitui uma chave estrangeira com relação à tabelaDISCIPLINA. As setas, acima, indicam as referências que de fato existem entre os registros das duas tabelas. Um dos tipos de violação da integridade referencial ocorre quando um registro referenciado é removido da base de dados. Por exemplo, suponha que o registro da disciplina Português seja removido. Há dois caminhos a seguir: um é recusar a operação; outro é remover também os registros que o referenciam, ou seja, remover todas as inscrições nesta disciplina. Este efeito é chamado remoção em cascata. O segundo tipo de violação ocorre quando há uma alteração no código de alguma disciplina referenciada. Por exemplo, se o código da disciplina Português fosse alterado para 417, as refe- rências existentes tornar-se-iam inválidas. Novamente, há dois cursos de ação: recusar a opera- ção, mantendo a integridade, ou promover a mesma alteração em todos os registros das inscri- ções, de modo a manter íntegras as referências. Este segundo efeito é chamado de propagação de atualização. Alguns SGBD oferecem uma terceira alternativa nos dois casos acima, qual seja a de preencher com nulos as referências que tornam-se inválidas. Embora isto não preserve a integridade refe- rencial, pelo menos é possível completar a operação e, mais adiante, tratar os registros atingidos. Quanto à qualidade da referência, pode-se classificar o nível de integridade como match partial e match full. Para chaves estrangeiras compostas por mais de um atributo, match full significa que todos os atributos devem estar preenchidos, sendo a referência completa. Com match par- tial, presume-se ser aceitável haver alguns desses atributos componentes preenchidos com nu- los. Esses aspectos são analisados em maiores detalhes nas seções que tratam dos aspectos específi- cos de cada implementação SQL. CHAVES CANDIDATAS Ocasionalmente, é preciso que, além da chave primária, outros atributos ou conjuntos de atribu- tos tenham a propriedade de serem únicos numa tabela. A cláusula unique pode ser utilizada com este fim. No banco de exemplos, as tabelas DISCIPLINA e PROFESSOR têm, entre si, um relacio- namento 1 para 1. Isto significa que um mesmo professor não pode estar ligado a mais de uma disciplina. Tal restrição pode ser resolvida através de uma chave candidata: basta fazer com que o atributo codprofessor, na tabela DISCIPLINA, seja um identificador. Supondo, ainda, que os atri- butos curso, na tabela CURSO, e disciplina, na tabela DISCIPLINA, não possam ser repetidos em dife- rentes registros, as declarações dessas tabelas poderiam ser construídas como create table curso ( codcurso int not null, curso varchar (40) not null, constraint pk_curso primary key (codcurso), constraint uq_curso unique (curso) ) create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float not null, chsp float not null, constraint pk_disciplina primary key (coddisciplina), constraint fk_disciplina_professor foreign key (codprofessor) references professor, constraint uq_disciplina unique (disciplina), constraint up_codprofessor unique (codprofessor) ) Assim como a chave primária, a propriedade unique poderia aplicar-se a um conjunto de atribu- tos. Uma mesma tabela pode ter várias chaves candidatas (efeito equivalente pode ser obtido a 216 Capítulo 11: Criando, eliminando e modificando tabelas partir de índices sem duplicações, vistos no capítulo 14). Chaves candidatas são também um tipo de constraint e, portanto, podem receber nomes. VALORES DEFAULT Muitas vezes é preciso incluir registros ainda que nem todos os atributos estejam preenchidos. Quando isso ocorre, a possibilidade de se definir valores padronizados para cada coluna é im- portante, pois mantém o registro íntegro e evita a ocorrência de valores nulos e suas inconveni- ências (vide capítulo 4). A palavra-chave default introduz a expressão que deve preencher o atributo no caso de ausência de valor para o mesmo na ocasião da inclusão do registro. create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float default 0 not null, chsp float default 0 not null, constraint pk_disciplina primary key (coddisciplina) constraint fk_disciplina_professor foreign key (codprofessor) references professor, constraint uq_disciplina unique (disciplina), constraint up_codprofessor unique (codprofessor) ) create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float not null default 0, chsp float not null default 0, constraint pk_disciplina primary key (coddisciplina) constraint fk_disciplina_professor foreign key (codprofessor) references professor, constraint uq_disciplina unique (disciplina), constraint up_codprofessor unique (codprofessor) ) As expressões que podem ser usadas para definir os valores default dos atributos dependem da implementação; em geral, são permitidas expressões aritméticas que incluem funções intrínse- cas. No nosso exemplo, as cargas horárias teórica e prática, se omitidas, assumem o valor zero. No Access, a cláusula default não é aceita no SQL. Oracle e SQL Server supõem as cláusulas not null e default em ordens diferentes, o que requer duas versões para o comando acima. RESTRIÇÕES DE VALORES (CLÁUSULA CHECK) Os valores que um atributo pode assumir são determinados pelo domínio de seu tipo de dados correspondente. O tipo int, por exemplo, permite que o atributo armazene valores inteiros num certo intervalo. Entretanto, muitas vezes é preciso restringir ainda mais esses valores. Por e- xemplo, a nota de um aluno deve ser um valor real entre 0 e 10. Como não há um tipo de núme- ros reais neste intervalo particular, é preciso empregar um tipo numérico mais abrangente e criar uma restrição suplementar. Outro tipo comum de restrição é o controle da correlação entre dife- rentes atributos numa tabela. Supondo-se que a carga horária teórica das disciplinas deva ser, sempre, pelo menos igual à metade da carga horária prática, poder-se-ia refinar a declaração da tabela DISCIPLINA, com auxílio da cláusula check, como mostrado a seguir. create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float not null default 0 check (chst >= 0), chsp float not null default 0 check (chsp >= 0), constraint pk_disciplina primary key (coddisciplina), Declarando as características de uma tabela 217 constraint fk_disciplina_professor foreign key (codprofessor) references professor, constraint uq_disciplina unique (disciplina), constraint up_codprofessor unique (codprofessor), constraint carga_horaria check ( chst >= 0.5 * chsp ) ) create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float default 0 not null check (chst >= 0), chsp float default 0 not null check (chsp >= 0), constraint pk_disciplina primary key (coddisciplina), constraint fk_disciplina_professor foreign key (codprofessor) references professor, constraint uq_disciplina unique (disciplina), constraint up_codprofessor unique (codprofessor), constraint carga_horaria check ( chst >= 0.5 * chsp ) ) Para a tabela ALUNO poderíamos imaginar duas restrições adicionais: que o sexo seja “M” ou “F” e que a data de nascimento esteja num intervalo plausível.2 Uma cláusula check é formada por uma expressão lógica cujo valor final deve ser verdadeiro ou desconhecido. Note que é possível empregar os operadores in e between nas expressões que constituem uma cláusula check, assim como quaisquer outros construtores de expressões vistos no capítulo 4. create table aluno ( matricula int not null, nome varchar (40) not null, sexo char (1), codcurso char (3) not null, nascimento datetime, constraint pk_aluno primary key (matricula), constraint fk_aluno_curso foreignkey (codcurso) references curso, constraint aluno_sexo check ( sexo in ( 'M', 'F' ) ), constraint data_nascimento_válida check ( nascimento between '01/01/1950' and '31/12/2000' ) ) create table aluno ( matricula int not null, nome varchar (40) not null, sexo char (1), codcurso char (3) not null, nascimento datetime, constraint pk_aluno primary key (matricula), constraint fk_aluno_curso foreign key (codcurso) references curso, constraint aluno_sexo check ( sexo in ( 'M', 'F' ) ), constraint data_nascimento_válida check ( nascimento between '1950-01-01' and '2000-12-31' ) ) create table aluno ( matricula int not null, nome varchar (40) not null, sexo char (1), codcurso char (3) not null, nascimento date, constraint pk_aluno primary key (matricula), constraint fk_aluno_curso foreign key (codcurso) references curso, constraint aluno_sexo check ( sexo in ( 'M', 'F' ) ), constraint data_nascimento_válida check ( nascimento between to_date ('01/01/1950', 'dd/mm/yyyy') and to_date ('31/12/2000', 'dd/mm/yyyy') ) ) 2 O MySQL aceita sintaticamente a cláusula check mas não a implementa. O Access não aceita a cláusula check. 218 Capítulo 11: Criando, eliminando e modificando tabelas No último comando é preciso especificar as datas através da função to_date (vista no capítulo 8). ATRIBUTOS AUTONUMERADOS Em muitos casos, a utilização de atributos cujos valores são gerados automaticamente, sem re- petições, são de grande interesse em bancos de dados. No nosso banco exemplo, poderíamos imaginar que as matrículas dos alunos fossem geradas progressivamente, cada vez que um regis- tro fosse adicionado à tabela ALUNO. Com isso, não haveria o risco de existir matrículas repetidas e nem seria preciso cuidados com o controle da numeração. Esta facilidade é provida pelas implementações SQL, porém de formas bastante distintas umas das outras. Por esse motivo, optamos por apresentar cada uma separadamente, nas seções que tratam de detalhes das implementações SQL. 11.3 ELIMINANDO TABELAS Eliminar uma tabela é extremamente fácil. O comando drop table segue a sintaxe drop table <tabela> que é auto-explicativa. Para eliminar a tabela PROFESSOR, por exemplo, bastaria o comando drop table professor Eliminar uma tabela que está sendo referenciada por alguma chave estrangeira é uma potencial violação da integridade referencial, que provoca erro na operação. Em algumas implementa- ções, entretanto, é possível remover também as restrições de integridade ligadas à tabela sendo removida. Remover uma tabela significa: • remover todos os registros da tabela; • remover todos os índices criados para a tabela. 11.4 ALTERANDO AS CARACTERÍSTICAS DE UMA TABELA O comando alter table permite vários tipos de alterações numa tabela, tais como incluir, alterar e remover colunas e incluir e remover restrições de integridade. No Access não é possível alterar as propriedades de uma coluna através do SQL. Isso somente é possível através da interface de administração. ALTERANDO OS TIPOS DAS COLUNAS Para alterar o tipo de uma coluna, basta montar o comando com a opção alter column (ou mo- dify), seguida do nome da coluna sendo alterada e do novo tipo da coluna. alter table disciplina alter column disciplina varchar (50) not null alter table disciplina modify disciplina varchar (50) not null O comando acima altera o tipo do atributo disciplina para varchar(50) e especifica que nulos não são aceitos neste campo. Alterando as características de uma tabela 219 INCLUINDO NOVAS COLUNAS É possível alterar uma tabela pela introdução de novas colunas em sua estrutura, como no e- xemplo a seguir. alter table disciplina add creditos float not null default 0 alter table disciplina add creditos float default 0 not null alter table disciplina add creditos float not null Uma nova coluna, denominada créditos, é adicionada à tabela DISCIPLINA, com as características descritas. Note que , neste caso, as versões com a cláusula de valor default não são aceitas no Access. Em tabelas não vazias, novas colunas devem permitir nulos ou possuir algum valor default; caso contrário, inserir essas colunas sem uso de nulos seria uma potencial violação dos requisitos. Veja, mais adiante neste capítulo, como cada implementação procede nesta situação. REMOVENDO COLUNAS Colunas podem ser removidas através da cláusula drop column, como no exemplo a seguir. alter table disciplina drop column creditos A sintaxe é bastante simples. Eventuais constraints definidas sobre a coluna sendo eliminada devem ter sido removidas antes dessa operação. INCLUINDO CONSTRAINTS : CHAVES PRIMÁRIAS, CHAVES ESTRANGEIRAS E RESTRIÇÕES DE VALORES Qualquer um dos vários tipos de restrições de valores pode ser acrescentado a uma tabela atra- vés da cláusula add constraint. No exemplo abaixo, inclui-se a restrição de que o valor de uma hipotética coluna créditos deve ser igual ou superior à soma da carga horária teórica com a me- tade da carga horária prática da disciplina. alter table disciplina add constraint creditos_positivos check ( creditos >= chst + 0.5 * chsp ) As restrições são identificadas por seus respectivos nomes e não pode haver duplicação. Even- tuais restrições homônimas da que está sendo inserida devem ter sido previamente removidas. Os comandos a seguir mostram exemplos com outros tipos de restrições de integridade.3 alter table professor add constraint pk_professor primary key (codprofessor) ; alter table inscricao add constraint pk_inscricao primary key (matricula, coddisciplina) ; alter table disciplina add constraint uq_disciplina unique (disciplina) ; alter table disciplina add constraint fk_disciplina_professor foreign key (codprofessor) references professor ; No quarto comando, observe que quando uma chave estrangeira referencia a chave primária de outra tabela não é preciso listar os atributos que a compõem. Ainda que, na nossa base de e- 3 Algumas dessas restrições já foram incluídas nas tabelas por ocasião da sua definição, e são aqui repeti- das apenas a título de exemplo. 220 Capítulo 11: Criando, eliminando e modificando tabelas xemplos, as chaves estrangeiras refiram-se às chaves primárias das tabelas, isso não é uma obri- gatoriedade no modelo relacional. Qualquer conjunto de atributos que configure uma chave candidata (vide seção a respeito mais adiante) pode constituir uma chave de referência. REMOVENDO RESTRIÇÕES Restrições podem ser removidas através da cláusula drop constraint, como no exemplo a seguir. alter table disciplina drop constraint fk_disciplina_professor ∴ RESTRIÇÕES INCONSISTENTES Algumas alterações das características de uma tabela podem ou não ser bem sucedidas, depen- dendo da instância corrente no momento da operação. Por exemplo, se uma coluna que permite nulos é modificada para not null, é necessário que nenhum dos registros possua o valor nulo nesta coluna. Se já houver valores nulos, é necessário alterá-los todos, antes da mudança. Nu- ma tabela não vazia, novas colunas devem permitir nulos ou possuir algum valor default; caso, contrário, a inserção dessas colunas causaria inconsistências na base de dados, pois não haveria como preenchê-las. Outros exemplos em que novas restrições podem ser inválidas ocorrem para chaves estrangei- ras, índices sem duplicatas, restrições de valores e quaisquer outras exigências baseadas em condições lógicas, que devem ser verificadas na instância de uma ou mais tabelas. Veja como cada implementação trata esses casos nas seções específicas, neste capítulo. 11.5 CRIANDO, ELIMINANDO E MODIFICANDO TABELAS NO ACCESS Nem todas as alterações que o Access suporta podem ser descritas por comandos SQL. Na ver- dade, esses comandos são razoavelmente limitados,uma vez que supõe-se que o usuário faça uso da interface de gerenciamento. As seções a seguir detalham alguns aspectos relevantes sobre as possibilidades de alteração de tabelas no Access. SINTAXE Os comandos create table e alter table obedecem à sintaxe mostrada a seguir. <comando create table> := create table <tabela> ( { <definição de atributo> | <constraint geral> },... ) <comando alter table> := alter table <tabela> <operação> <operação> := { add column <definição de atributo> | add <constraint geral> | drop constraint <nome de constraint> | drop column <coluna> } <definição de atributo> := <atributo> <tipo> [ null | not null ] [ <constraint de atributo> ] <constraint geral> := constraint <nome de constraint> { { primary key | unique | not null } ( <coluna>,... ) | foreign key [ no index ] ( <coluna>,... ) references <tabela> [ ( <coluna>,... ) ] Criando, eliminando e modificando tabelas no Access 221 [ on update cascade | set null ] [ on delete cascade | set null ] } <constraint de atributo> := constraint <nome de constraint> { { primary key | unique | not null } | references <tabela> [ ( <coluna>,... ) ] [ on update cascade | set null ] [ on delete cascade | set null ] } Os comandos acima compreendem a criação de tabelas e as operações de alteração, incluindo a adição e remoção de colunas e constraints. Note que mesmo as restrições que aplicam-se a apenas um atributo, não sendo explicitamente nomeadas, são tratadas genericamente como constraints. NOMES DE TABELAS, ATRIBUTOS E RESTRIÇÕES Nomes podem ser bastante flexíveis no Access. Os nomes para tabelas, atributos e restrições podem ter até 64 caracteres, incluindo letras, números, espaços e caracteres especiais, exceto . (ponto), ! (exclamação), ` (acento grave) e [ ] (abre/fecha colchetes). Um nome não pode iniciar com o caracter de espaço e não pode incluir caracteres de controle (valores ASCII de 0 a 31). Embora sejam permitidos, caracteres especiais e espaços em nomes não são uma boa prática, pois podem causar ambigüidades no SQL. Uma tabela chamada #1 teste, por exemplo, requer o uso de colchetes num comando SQL, porque X select * from #1 teste provoca erros de sintaxe. O correto é referenciar essa tabela como select * from [#1 teste] Escrever colchetes em todas as referências a tabelas e atributos assim nomeados é um teste de paciência, que pode e deve ser evitado. ∴ O Access não difere entre maiúsculas e minúsculas nos nomes. PALAVRAS RESERVADAS O emprego de palavras reservadas no Access também é flexível. Dependendo do contexto, algumas palavras reservadas são aceitas. Por exemplo, a tabela TESTE pode ser criada como create table teste ( X having varchar (40) not null, between char (1) ) mesmo sendo os nomes dos atributos, having e between, palavras de uso reservado. Isso ocorre porque, no contexto da definição de atributos, essas duas palavras não têm papel sintático rele- vante. Um atributo chamado key ou unique, contudo, causaria um erro de sintaxe, pois essas últimas têm papel sintático na definição de um atributo. Note, porém, que mesmo aceitos na definição da tabela, os nomes dos atributos podem causar dores-de-cabeça mais tarde. Um comando SQL como select having X from teste where between > 12 não é aceito e deveria ser escrito select having from teste 222 Capítulo 11: Criando, eliminando e modificando tabelas where [between] > 12 Como norma geral, o mais seguro é procurar não utilizar palavras reservadas na definição de elementos do banco de dados. VALORES NULOS Access é liberal com valores nulos quando da criação de novas colunas em tabelas já instancia- das. Para tipos numéricos, mesmo que o atributo sendo criado não suporte nulos, as novas co- lunas assumem este valor ainda que algum valor padrão tenha sido especificado. Novos regis- tros (ou registros que sejam regravados), entretanto, são verificados quanto à presença de valo- res nulos. Para tipos não numéricos, uma coluna que não aceita nulos somente pode ser inserida se a tabela ainda estiver vazia. CHAVES PRIMÁRIAS (PRIMARY KEYS) A definição de uma chave primária implica na criação de um índice sem duplicação, que a im- plementa (vide capítulo 14). Um índice decorrente da definição da chave primária não admite valores nulos. INTEGRIDADE REFERENCIAL (CHAVES ESTRANGEIRAS) No Access, é possível associar, para cada chave estrangeira, separadamente, os efeitos de remo- ção em cascata e/ou de propagação de atualização. Também é possível alterar para nulos os atributos atingidos quando um registro referenciado por uma chave estrangeira é removido ou alterado. VALORES DEFAULT O Access não aceita a especificação de valores default através do SQL; contudo, através da interface, qualquer expressão aritmética (podendo incluir literais e funções) é válida na designa- ção do valor default de um atributo. CHAVES CANDIDATAS Toda chave candidata é implementada através de índice sem duplicação. Um índice decorrente da definição de chaves candidatas pode admitir valores nulos. Vide capítulo 14 para maiores detalhes. RESTRIÇÕES DE VALORES Restrições de valores não podem ser especificadas através da cláusula check do SQL, embora seja possível fazê-lo pela interface do Access. ATRIBUTOS AUTONUMERADOS Atributos autonumerados podem ser definidos pelo tipo autoincrement (ou pelo seu sinônimo, o tipo counter). Por exemplo, se a tabela ALUNO utilizasse este recurso para a geração de núme- ros de matrícula automaticamente, a mesma poderia ter sido criada como create table aluno ( matricula autoincrement (1000,10) not null constraint pk_aluno primary key, Criando, eliminando e modificando tabelas no MySQL 223 nome varchar (40) not null, sexo char (1), codcurso char (3), nascimento datetime, constraint fk_aluno_curso foreign key (codcurso) references curso ) Uma coluna autonumerada é implementada com o tipo long (sinônimo de int, integer, e inte- ger4). Depois de criada, uma coluna de autonumeração pode ter seu tipo alterado para outro qualquer, sem essa propriedade, desde que seja possível fazer a conversão dos valores. Entre- tanto, uma coluna normal não pode ser transformada numa coluna com autonumeração. É possível estabelecer o valor inicial e o incremento da numeração. No exemplo acima, os re- gistros serão numerados de 10 em 10 a partir de 1000. Se nada for especificado, a numeração dos registros é gerada a partir de 1, com incremento de 1. Quando uma coluna de autonumera- ção é adicionada a uma tabela, todos os registros eventualmente existentes são numerados au- tomaticamente. É possível descobrir qual foi o último valor de autoincremento utilizado em alguma tabela com a variável predefinida @@identifity, como no comando abaixo. select @@identify Note que não é possível especificar uma tabela específica. É mostrado sempre o último valor utilizado, independentemente da tabela ao qual está associado. Os valores inicial e de incremento de uma autonumeração podem ser alterados depois da criação da tabela. O comando alter table aluno alter column matricula autoincrement (50000,100) faz com que os novos valores passema a ser numerados a partir de 50000, com incrementos de 100. É preciso atenção num comando desse tipo porque os novos valores de vem ser compatí- veis com os eventuais registros que já existam na tabela. 11.6 CRIANDO, ELIMINANDO E MODIFICANDO TABELAS NO MYSQL No MySQL, o tamanho máximo de cada tabela depende do tamanho permitido para os arquivos do sistema operacional. Contudo, tabelas do tipo MyISAM têm um tamanho máximo default de 4 GBytes, que pode ser estendido com a utilização das opções de tabela avg_row_length e max_rows. Cada tabela pode ter, no máximo, 32 índices (podendo este limite ser aumentado para 64). SINTAXE A sintaxe dos comandos create table e alter table é apresentadaa seguir. <create table> := create [ temporary ] table [ if not exists ] <tabela> { ( { <definição de atributo> | <restrição geral> },... ) [<opções de tabela>... ] [ [ ignore | replace ] <comando select> ] | like <tabela> } <alter table> := alter [ ignore ] table <tabela> <operação>,... <operação> := { add [ column ] <definição de atributo> [ first | { after <coluna> } ] | add [ column ] ( <definição de atributo> ,... ) | add < restrição geral > | drop [ column ] <coluna> | drop primary key 224 Capítulo 11: Criando, eliminando e modificando tabelas | drop index <índice> | alter [ column ] <coluna> { set default <literal> | drop default } | change [ column ] <coluna> <definição de atributo> [ first | after <coluna> ] | modify [ column ] <definição de atributo> [ first | after <coluna> ] | { enable | disable } keys | rename [ to ] <tabela> | order by { <atributo> [ asc | desc ] },... | <opções de tabela> } <definição de atributo> := <coluna> <tipo de dados> [ unsigned ] [ zerofill ] [ <restrição de atributo>,... ] <restrição geral> := { [ primary ] key ( <atributo>,... ) | index [ <índice> ] ( <atributo>,... ) | unique [ <índice> ] ( <atributo>,... ) | fulltext [ <índice> ] ( <atributo>,... ) | check ( <expressão> ) | [ [ constraint ] <restrição> ] foreign key <índice> ( <atributo>,... ) <referência estrangeira> } <restrição de atributo> := { [ not ] null | default <valor> | auto_increment | [ primary ] key | <referência estrangeira> } <referência estrangeira> := references <tabela> [ ( <atributo>,... ) ] [ match full | match partial ] [ on delete { restrict | cascade | set null | no action | set default } ] [ on update { restrict | cascade | set null | no action | set default } ] <opções de tabela> := { type = { bdb | heap | isam | innodb | merge | mrg_myisam | myisam } | auto_increment = <número> | avg_row_lenght = <número> | checksum = { 0 | 1 } | comment = " <string> " | max_rows = <número> | min_rows = <número> | packkeys = { 0 | 1 | default } | password = " <string> " | delay_key_write = { 0 | 1 } | row_format = { default | dynamic | fixed | compressed } | raid_type = { 1 | striped | raid0 } raid_chunks = <número> raid_chunksize = <número> | union = ( <tabela>,... ) | insert_method = { no | first | last } | data directory = " <path> " | index directory = " <path> " } O significado das construções desta sintaxe e seus aspectos mais relevantes são discutidos a seguir. MOSTRANDO AS TABELAS Inicialmente, é importante saber como obter a definição corrente de cada tabela num banco de dados MySQL. Com isso, é possível conferir o efeito dos comandos aqui mostrados e exempli- ficados. O comando show tables mostra as tabelas existentes num banco de dados. O resultado é uma lista de nomes que inclui todas as tabelas permanentes do banco de dados. ∴ Criando, eliminando e modificando tabelas no MySQL 225 O comando describe (ou desc) exibe as principais informações sobre a estrutura de uma tabela. { describe | desc } <tabela> { <coluna> | <string de comparação> } Em tabelas com muitas colunas, pode-se especificar alguma coluna em particular ou fornecer uma string genérica, que pode conter os caracteres curinga % (percentual), significando qual- quer string , e _ (sublinhado), significando qualquer caracter. Por exemplo, o comando describe aluno produz a listagem do quadro a seguir. Field Type Collation Null Key Default Extra matricula int(11) binary PRI 0 nome varchar(40) latin1_swedish_ci sexo char(1) latin1_swedish_ci YES NULL codcurso char(3) latin1_swedish_ci YES NULL nascimento date latin1_swedish_ci YES NULL 5 rows in set (0.00 sec) Um comando como desc disciplina chst produz resultado semelhante, porém mostrando somente a coluna chst. Já o comando describe disciplina 'ch%' produz informações sobre as colunas chst e chsp. ∴ O comando show create table é útil para exibir as características de uma tabela. Usando, por exemplo, show create table aluno é possível conhecer todas as características correntes da referida tabela, na forma de um coman- do create table remontado. TIPOS DE TABELAS MySQL trabalha com tabelas de vários tipos, que dividem-se em dois grupos: tabelas com su- porte a transações e tabelas sem suporte a transações. MyISAM MERGE HEAP Tabelas sem suporte a transações ISAM InnoDB Tabelas com suporte a transações BDB Todas as tabelas têm um arquivo .frm que contém suas definições. Tabelas que não suportam transações requerem menos espaço e operam mais rapidamente, pois não há necessidade das tarefas extras do sistema por conta desse gerenciamento. Tabelas com suporte à transação per- mitem, por exemplo, operações de rollback, pois mantêm atas (logs) de todas as suas operações. Tabelas MyISAM É o tipo default no MySQL. Têm seus dados gravados em arquivos .myd e índices gravados em arquivos .myi. Utilizam índices do tipo B-tree4 e podem existir sob três formatos: static (regis- tros de tamanho fixo), dynamic (registros de tamanho variável) e compressed (formato para 4 Veja detalhes sobre índices no capítulo 14. 226 Capítulo 11: Criando, eliminando e modificando tabelas leitura apenas). Tabelas deste tipo são armazenadas em arquivos no diretório do banco de da- dos. Para uma tabela denominada TESTE, por exemplo, criam-se os arquivos5 tes e.frmt t t definições para a tabela tes e.myd dados armazenados tes e.myi estruturas de índices Tabelas MERGE (ou MRG_MyISAM) Tabelas desse tipo são formadas a partir da união de outras tabelas MyISAM. Com isso, pode-se operar sobre as tabelas componentes simultaneamente, com comandos como select, update ou delete. Também são úteis para a consolidação de dados, uma vez que tabelas de diferentes fon- tes podem ser unificadas facilmente. Contudo, há várias limitações em relação aos comandos permitidos sobre elas, além de restrições de uso das tabelas componentes de uma tabela MERGE quando esta está em uso. O termo MRG_MyISAM pode ser empregado como sinônimo de MERGE. Tabelas HEAP São tabelas armazenadas exclusivamente na memória, que utilizam índices do tipo hash sendo, portanto, extremamente rápidas. Não há garantia alguma contra falhas do sistema. Tabelas desse tipo são usualmente empregadas como tabelas temporárias, de fácil reconstrução em caso de pane. Há várias restrições sobre as operações que podem suportar, além de cuidados a tomar por ocasião da sua criação. Tabelas ISAM Este tipo de tabela foi sobrepujado pelo tipo MyISAM e tende a desaparecer, embora ainda possa ser utilizado em algumas versões. Tabelas InnoDB InnoDB, na verdade, constitui uma máquina de banco de dados que suporta transações, recupe- ração de informação, locks e multi-uso, e outras características. Diferentemente das tabelas MyISAM, que armazenam seus dados e índices em arquivos específicos, tabelas InnoDB arma- zenam dados e índices em tablespaces, que podem abranger vários arquivos. Algumas instala- ções devem ser explicitamente configuradas para abrigar esse tipo de tabela. Tabelas BDB São tabelas criadas na máquina BerkeleyDB e suportam controle de transações, commit e roll- back. Essas tabelas são suportadas primordialmente em instalações Unix. Algumas instalações devem ser explicitamente configuradas para abrigar tabelas desse tipo. ∴ O tipo de uma tabela é estabelecido no comando create table, como no exemplo a seguir. create table teste ( a varchar (40) not null, b int ) type = myisam Uma tabela já existente pode ser convertida para um outro tipo. O comando alter table teste type = innodb altera o tipo da tabela criada acima. Se for escolhidoum determinado tipo de tabela que não esteja disponível na instalação, MySQL cria a tabela usando um tipo assemelhado (em geral, MyISAM). Por exemplo, a indisponibili- dade do tipo BDB acarreta a utilização do tipo MyISAM como alternativa. ∴ 5 Os nomes dos arquivos preservam maiúsculas e minúsculas empregadas no nome da tabela. Criando, eliminando e modificando tabelas no MySQL 227 Muitas vezes, é comum que durante o teste de uma base de dados seja preciso repetir os coman- dos de criação de tabelas até que o projeto esteja estabilizado. Nessas situações, é interessante utilizar a opção if not exists, disponível no MySQL, que evita erros na criação de uma tabela quando a mesma já existe. Por exemplo, o comando create table if not exists teste ( a varchar (40) not null, b int ) somente leva adiante a criação da tabela se ainda não existir tabela como o mesmo nome. Note que a tabela existente não precisa ser idêntica à que está sendo criada; basta que tenham nomes iguais. NOMES Os nomes para bancos de dados e tabelas podem ter até 64 caracteres. Em função do método de armazenagem de dados do MySQL, nomes de bancos seguem as mesmas regras para pastas do sistema operacional, exceto os caracteres . (ponto), / (barra) e \ (barra invertida). Os nomes de tabelas seguem as mesmas regras dos nomes de arquivos, exceto pelos caracteres . (ponto) e / (barra) . Nomes de colunas podem ter até 64 caracteres e aceitam qualquer caracter; nomes alternativos, usados em cláusulas as, podem ter até 255 caracteres e também aceitam qualquer caracter. Em todos os casos, contudo, excetuam-se os caracteres ASCII de códigos 0 e 255, além dos caracteres que denotam aspas (simples ou duplas). Note que isso implica em cuidados quando utilizam-se sistemas operacionais distintos, uma vez que as regras de denominação de pastas e arquivos podem diferir de ambiente para ambiente. Outra conseqüência disso é que nomes de bancos de dados e tabelas passam a ser diferenciados em maiúsculas e minúsculas, uma vez que alguns sistemas operacionais, como Unix, assim o fazem. Nomes alternativos de tabelas, introduzidos pelas cláusulas as, diferenciam maiúsculas e minúsculas e requerem cuidado. Um comando como X select A.nome from aluno a provoca erro, pois a variável de registro A não é reconhecida. Uma forma de evitar equívocos com caixas altas e baixas é utilizar a opção lower_case_table_names ligada, pois a mesma força os nomes de bancos e tabelas para caixa baixa. ∴ Espaços e caracteres especiais em nomes continuam não sendo uma boa idéia, pois podem cau- sar ambigüidades. Uma tabela chamada teste duplo, por exemplo, requer o uso do delimitador ` (acento grave) num comando SQL, porque select * from teste duplo provoca erros de sintaxe. O correto é referenciar essa tabela como abaixo. select * from `teste duplo` É sempre bom lembrar: jamais use nomes contendo espaços ou caracteres especiais, exceto o caracter _ (sublinhado). PALAVRAS RESERVADAS O comando create table teste ( having varchar (40) not null, between char (1) ) 228 Capítulo 11: Criando, eliminando e modificando tabelas não é aceito porque o MySQL proíbe expressamente o uso de palavras reservadas na designação de nomes, qualquer que seja o contexto. Entretanto, um comando com os mesmos nomes com o delimitador ` (acento grave) create table teste ( `having` varchar (40) not null, `between` char (1) ) é aceito. Mas tudo tem seu preço. Invocar esses atributos requer delimitadores, que podem ser ` (acento grave), ' (apóstrofo) ou " (aspas duplas). O nome da tabela aceita somente o delimitador ` (acento grave). Assim, os comandos select “having” from teste select 'having' from teste select `having` from teste funcionam, mas o comando X select having from teste não funciona. TIPOS MODIFICADOS Em alguns casos, o MySQL promove pequenas alterações nos tipos escolhidos para os atributos, tanto na criação de uma tabela como na sua alteração. Isso acontece principalmente em atribu- tos que armazenam strings. Veja mais detalhes no capítulo 7, na seção que trata dos tipos no MySQL. TIPOS NUMÉRICOS COM UNSIGNED E ZEROFILL Um tipo numérico pode ser modificado com a opção unsigned, como foi explicado no capítulo 7. Normalmente, um tipo numérico aceita valores positivos e negativos. A opção unsigned de- termina que valores negativos não sejam aceitos. ∴ Quando um atributo de tipo numérico é definido com a propriedade zerofill, os valores dessa coluna são preenchidos com zeros à esquerda, até o tamanho do campo, toda vez que são mos- trados no resultado de uma consulta. Por exemplo, se a tabela DISCIPLINA tivesse sido definida como no comando abaixo create table disciplina ( coddisciplina int not null, disciplina varchar (40) not null, codprofessor int, chst float not null default 0 check (chst >= 0), chsp float zerofill not null default 0 check (chsp >= 0), constraint pk_disciplina primary key (coddisciplina), constraint fk_disciplina_professor foreign key (codprofessor) references professor, constraint uq_disciplina unique (disciplina), constraint up_codprofessor unique (codprofessor), constraint carga_horaria check ( chst >= 0.5 * chsp ) ) o resultado de um comando select para todos os registros da tabela seria mostrado como coddisciplina disciplina codprofessor chst chsp 112 Dir. Constitucional 15 4 00000000000 114 Direito Civil 2 4 00000000002 117 Estatística 12 2 00000000002 210 Compiladores 17 2 00000000004 Criando, eliminando e modificando tabelas no MySQL 229 211 Bancos de Dados 10 4 00000000002 316 Sociologia 18 3 00000000001 317 Português 3 4 00000000000 O número de zeros depende do tamanho declarado para o atributo. Esse efeito não perdura quando o atributo participa de uma expressão aritmética, pois passam a prevalecer as regras usuais de exibição de valores. TABELAS DO TIPO MERGE Sendo definidas a partir de outras tabelas, previamente criadas, as tabelas do tipo MERGE, na verdade, têm apenas sua composição definida no comando create, o que muda sensivelmente a sintaxe do comando. Por exemplo, supondo que M1, M2 e M3 sejam tabelas com estruturas idên- ticas create table m1 ( a varchar (40), b int ) create table m2 ( a varchar (40), b int ) create table m3 ( a varchar (40), b int ) a criação de uma tabela TESTEM, poderia ser feita através do comando create table testeM ( a varchar (40), b int ) type = merge union = (m1, m2, m3) insert_method = last Embora a estrutura deva ser idêntica à estrutura das tabelas que a compõem, é preciso repeti-la no comando create table. A opção insert_method indica que os registros inseridos na tabela MERGE sendo criada devem, de fato, ser inseridos na última tabela que faz parte da união. Similarmente, o parâmetro first escolheria a primeira tabela como alvo dos registros. Veja, no capítulo 12, mais detalhes sobre o efeito dos comandos de inserção, remoção e alteração de registros em tabelas deste tipo. REFERENCIANDO TABELAS EM OUTROS BANCOS DE DADOS Normalmente, os comandos referenciam tabelas no banco de dados corrente. Entretanto, co- mandos onde os nomes das tabelas aparecem precedidos pelo nome de um banco de dados têm efeito no banco que é referenciado. Assim, o comando drop table db1.teste tem o efeito de remover a tabela TESTE de um banco de dados denominado db1. 230 Capítulo 11: Criando, eliminando e modificando tabelas TABELAS TEMPORÁRIAS Uma tabela pode ser criada como temporária, o que significa que ela existe, e é visível, apenas para o usuário que a criou, no tempo da atual conexão. Ao ser encerrada a conexão, extingue-se a tabela. Uma tabela temporária não é detectada por outros usuários, o que permite que mais de um usuário tenham, simultaneamente, tabelas temporárias com os mesmos nomes. Tabelas temporáriassão criadas pela palavra-chave temporary no comando create table, como no e- xemplo a seguir. create temporary table teste ( a varchar (40) not null, b int ) Se for criada uma tabela temporária com o mesmo nome de uma tabela permanente exis- tente, o MySQL “esconde” a tabela permanente e toda referência àquele nome é interpre- tada como uma referência à tabela temporária. Quando a tabela temporária deixa de exis- tir (seja pelo término da conexão ou por um comando drop table), a tabela permanente volta a estar disponível e visível. VALORES NULOS Quando uma nova coluna é inserida numa tabela já instanciada, e essa coluna não aceita nulos e não possui valor default, MySQL preenche o atributo da nova coluna, nos registros eventual- mente existentes, com zeros se o tipo for numérico ou com a string vazia, se o tipo for de carac- teres. Colunas com tipos como date e datetime são também preenchidas com zeros. Veja mais detalhes na seção Valores default, mais adiante neste capítulo. CHAVES PRIMÁRIAS (PRIMARY KEYS) A chave primária de uma tabela é implementada através de um índice, que é automaticamente gerado quando da criação da tabela. Nenhum dos atributos que compõem uma chave primária pode assumir o valor nulo. Quando a chave primária de uma tabela é constituída por apenas um atributo de um tipo inteiro, é possível fazer referência a essa coluna pelo sinônimo _rowid. Assim, um comando como select _rowid from aluno retornaria a lista de matrículas dos alunos, porque o atributo matrícula, sozinho, compõe a cha- ve primária da referida tabela. O uso deste nome não impede o uso do nome real da coluna. A consulta select _rowid, nome from aluno order by matricula é válida, e retorna matrículas e nomes dos alunos ordenados pela primeira coluna. ∴ Quando uma tabela não tem uma chave primária especificada, MySQL atribui esse papel à pri- meira chave candidata (definida como unique) que não admita nulos. Isso é válido para o iden- tificador _rowid: em não havendo uma chave primária, a coluna que eventualmente tem este papel é referenciada quando o atributo _rowid é invocado. ∴ As definições de chaves primárias são removidas com a cláusula drop primary key, do comando alter table. O comando alter table aluno drop primary key Criando, eliminando e modificando tabelas no MySQL 231 remove a chave primária da tabela ALUNO. INTEGRIDADE REFERENCIAL (CHAVES ESTRANGEIRAS) Restrições de integridade referencial somente são verificadas entre tabelas do tipo InnoDB. Nos demais tipos, o MySQL aceita sintaticamente a declaração, mas não a implementa. A sintaxe permite que sejam especificadas ações nos casos em que os valores dos atributos refe- renciados são alterados, ou quando registros sendo referenciados são removidos (vide seção 11.2). Cascade implica na ação de remoção em cascata; set null significa preencher com nulos as chaves estrangeiras que tornam-se referências inválidas; no action significa aceitar passiva- mente e restrict significa manter a integridade. Por exemplo, o comando para determinar que existe integridade referencial entre as tabelas ALU- NO e CURSO, e que remoções devem ser restringidas e alterações propagadas em cascata é alter table curso constraint fk_aluno_curso foreign key (codcurso) references curso (codcurso) on delete restrict on update cascade Esta mesma sintaxe vale no comando create table. Note que, tanto na tabela que contém a cha- ve estrangeira como na tabela que é referenciada, é preciso que os atributos constituam um índi- ce (MySQL aceita um onde haja outros atributos desde que os atributos da chave estrangeira sejam os primeiros da lista). Esses índices devem existir antes do comando que cria a chave estrangeira ser executado. MySQL permite a especificação das opções match full e match par- tial. VALORES DEFAULT No MySQL, somente constantes podem definir valores default. Isso significa que funções e expressões não são admitidas. Quando o valor default não é expressamente especificado na definição de uma coluna, o próprio MySQL o designa, de acordo com a seguintes regras: Colunas e seus tipos Valores default designados pelo MySQL Colunas com tipos numéricos, não definidas como auto- numeração Zero Colunas com tipos numéricos, definidas como autonume- ração Preenchida de acordo com as regras de autonumeração Colunas com tipo timestamp Data e hora correntes Colunas com tipos de registro de tempo (date, time) mas não timestamp Um registro de tempo, de acordo com o tipo da coluna, com todos os componentes zerados Colunas com tipos string de caracteres A string vazia Colunas com tipos enum Um conjunto com o primeiro elemento enumerado CHAVES CANDIDATAS São criadas através da construção unique e implementadas através de índices que não admitem duplicação. Valores nulos são admitidos em chaves candidatas, ao contrário do que acontece com as chaves primárias. Se uma tabela não possui uma chave primária definida, a primeira chave candidata que não admite nulos passa a ter este papel. 232 Capítulo 11: Criando, eliminando e modificando tabelas RESTRIÇÕES DE VALORES A sintaxe permite, mas o MySQL não implementa. ATRIBUTOS AUTONUMERADOS Atributos autonumerados podem ser definidos pela propriedade auto_increment em colunas de tipo inteiro (int, bigint, smallint, etc.) que sejam indexadas, isto é, possuam a propriedade pri- mary key (ou unique). No exemplo a seguir, o atributo a, da tabela TESTE, é do tipo smallint e tem a propriedade de autonumeração. create table teste ( a smallint auto_increment unique, b int ) Somente uma das colunas de uma tabela pode ter a propriedade de autonumeração e é preciso que a mesma seja indexada. Isto pode ser obtido pela definição de uma chave primária, da pro- priedade unique ou através de índice explícito, como apresentado no capítulo 14. O próximo valor de um atributo autonumerado pode ser estabelecido através da opção au- to_increment. No exemplo create table teste ( a smallint auto_increment unique, b int ) auto_increment = 1000 cria-se a tabela de tal forma que o primeiro registro inserido terá o valor 1000 no seu atributo a. Note que o próximo valor pode ser estabelecido mesmo para uma tabela já existente. Um co- mando como alter table teste auto_increment = 2000 faria com que o próximo registro incluído recebesse o valor 2000, no seu atributo autonumera- do. ∴ Uma possibilidade interessante, no MySQL, é a utilização de atributos autonumerados em con- junto com outros atributos, de modo a vincular seus valores, numa espécie de autonumeração hierárquica. Vamos imaginar que nossa tabela de inscrições requeira um numeração para cada inscrição de um aluno, a partir de 1, numeradas por aluno. Assim, a definição da tabela INSCRIÇÃO poderia ser create table inscricao ( matricula int not null, sequencia int not null auto_increment, coddisciplina int not null, nota float null, constraint pk_inscricao primary key (matricula, sequencia), constraint uq_inscricao unique (matricula, coddisciplina), constraint fk_inscricao_aluno foreign key (matricula) references aluno (matricula), constraint fk_inscricao_disciplina foreign key (coddisciplina) references disciplina (coddisciplina) ) O atributo autonumerado seqüência faz parte da chave primária, juntamente com o atributo ma- trícula. Após a inserção dos nove registros do banco de exemplos, o conteúdo da tabela seria o que é mostrado a seguir. matricula sequencia coddisciplina nota 1001 1 112 NULL Criando, eliminando e modificando tabelas no MySQL 233 1001 2 317 8,0 1002 1 210 9,5 1005 1 316 3,0 1005 2 117 4,2 1007 1 112 NULL 1007 2 114 7,0 1010 1 317 5,5 1010 2 316 10,0 Note que o atributo seqüência não precisa ser preenchido, uma vez que isso é feito automatica- mente pelo mecanismo de autonumeração. Contudo, como o atributoautonumerado faz parte da chave primária da tabela, a numeração é relativa ao valor do atributo matrícula. Em casos como este, não tem efeito a opção auto_increment, que altera o próximo valor de um atributo autonumerado. OUTRAS OPÇÕES PARA TABELAS MySQL admite várias opções adicionais, que complementam a definição de uma tabela, dentre as quais destacamos: data directory Especifica o caminho (path) completo para a pasta onde será gravado o arquivo de dados de uma tabela (.MYD). Funciona apenas para tabelas MyISAM e é afetada pelo estado da opção --skip-symlink; index directory Especifica o caminho (pa h) completo para a pasta onde será gravado o arquivo de índice de uma tabela (.MYI). Funciona apenas para tabelas MyISAM; t max_rows Estabelece uma estimativa do número máximo de registros de uma tabela; min_rows Estabelece uma estimativa do número mínimo de registros de uma tabela; avg_row_length Estabelece uma estimativa do tamanho médio dos registros de uma tabela. Aplicável para tabelas de tamanho variável, com muitos registros; password Criptografa, com uma palavra-chave, o arquivo de extensão .frm de uma tabela; row_format Determina o formato de armazenagem de uma tabela, podendo ser DYNAMIC ou FIXED; insert_method Determina se as inserções numa tabela tipo MERGE serão na primeira ou na última das tabelas que a compõem. Bloqueia as inserções se o seu valor for NO. Aplica-se somente a tabelas do tipo MERGE; pack_keys Opta pela compactação das chaves dos registros. Chaves compactadas significam índices menores, mais rápidos na leitura. CRIANDO TABELAS A PARTIR DE UM MODELO A opção like permite que uma tabela seja criada à semelhança de uma outra já existente. O comando create table aluno_novo like aluno causa a criação da tabela ALUNO_NOVO, exatamente com a mesma descrição e opções presentes na tabela ALUNO. Os registros da tabela modelo não são copiados. CRIANDO E INSTANCIANDO TABELAS Pode-se criar uma tabela cuja definição esteja baseada em outras tabelas ou no resultado de consultas SQL. Para isso, basta anexar um comando select no final do comando de criação da tabela. O comando create table teste type=innodb select * from inscricao 234 Capítulo 11: Criando, eliminando e modificando tabelas resulta na criação da tabela TESTE, do tipo InnoDB, cuja estrutura é idêntica à da tabela INSCRIÇÃO e que, inicialmente, recebe os mesmos registros presentes na tabela modelo. Note que, no comando acima, não foi preciso especificar a estrutura da tabela criada. Contudo, é possível sobrepor as definições embutidas na consulta, ou estender essa estrutura. Um exem- plo ilustra bem este ponto. create table teste ( numeracao int auto_increment unique, nota float not null ) type = innodb select matricula, coddisciplina, nota from inscricao where nota is null A regra que prevalece é simples: qualquer especificação de coluna que já exista no resultado do select aplica-se à mesma; as especificações de colunas que ainda não existam configuram colu- nas adicionais ao resultado, que passam a compor a tabela criada. A coluna nota já existe no select mas passa a ter a propriedade not null na tabela criada, mantendo o mesmo tipo float. A coluna autonumerada numeração não existe na consulta e, portanto, é uma coluna adicional. Note, contudo, que a instância da tabela criada, mostrada a seguir, passa a ter zeros no lugar de nulos, já que uma coluna numérica, que não permite nulos, tem zero como valor default. numeracao matricula codDdsciplina nota 1 1001 112 0 2 1007 112 0 Entretanto, se o comando utilizado tivesse sido create table teste ( numeracao int auto_increment unique, nota float not null ) type = myisam select matricula, coddisciplina, nota as nota_antes from inscricao where nota is null a tabela criada seria diferente porque nota seria uma coluna adicional, uma vez que a coluna nota original aparece no select com o título nota_antes. Nesse caso, a instância da tabela criada seria numeracao matricula coddisciplina nota nota_antes 1 1001 112 0 NULL 2 1007 112 0 NULL Os valores do atributo nota_antes agora são nulos, pois isso é permitido. Observe que a tabela criada é do tipo MyISAM. ∴ O comando select utilizado na consulta pode envolver mais de uma tabela de origem, como no exemplo create table teste ( numeracao int auto_increment unique, nota float not null ) type = myisam select i.matricula, nome, coddisciplina, nota as nota_antes from inscricao i inner join aluno a on i.matricula = a.matricula where nota is null ∴ Criando, eliminando e modificando tabelas no MySQL 235 Para criar uma tabela semelhante à estrutura de uma consulta, porém sem carregar seus regis- tros, basta que a consulta retorne um resultado vazio. Os dois comandos a seguir fazem uso desse recurso. create table teste type=innodb select * from inscricao limit 0 create table teste type=innodb select * from inscricao where 1=0 No primeiro caso, limita-se a zero o número de linhas no resultado; no segundo, a cláusula whe- re contém um termo lógico que é sempre falso, tornando impossível a inclusão de qualquer li- nha no resultado. Este recurso é comparável ao uso da palavra-chave like no comando create table, mas não é idêntico. Com a opção like, clona-se a descrição completa de uma tabela, incluindo suas carac- terísticas, como o tipo de tabela; no caso aqui mostrado, apenas a estrutura da consulta é apro- veitada. ∴ A opção ignore que precede o comando select, mostrada na sintaxe do comando create table, tem efeito quando uma modificação na tabela pode causar erro pela violação de identificadores únicos. Esse efeito é fazer com que os registros com identificadores duplicados sejam ignora- dos por ocasião da rearrumação da tabela. RENOMEANDO TABELAS Uma tabela pode ser renomeada com a opção rename, do alter table. Por exemplo, o comando alter table teste rename to teste_old faz com que a tabela TESTE passe a ser denominada TESTE_OLD. HABILITANDO E DESABILITANDO ÍNDICES As opções enable keys e disable keys, disponíveis no comando alter table, permitem “ligar” e “desligar” índices. Quando muitos registros são incluídos numa tabela de uma só vez, é interes- sante adiar a atualização de alguns de seus índices para uma etapa posterior, assim reduzindo significativamente o tempo de processamento. Como exemplo, o comando abaixo “desliga” os índices da tabela ALUNO. alter table aluno disable keys Essas cláusulas somente surtem efeito quando aplicadas a índices que não admitem dupli- catas. Veja mais detalhes sobre este tópico no capítulo 14. REORDENANDO OS REGISTROS DE UMA TABELA Uma tabela pode ter a disposição física de seus registros alterada através da opção order by do comando alter table. Isso é útil quando existe algum tipo de processamento corriqueiro que é facilitado se os registros estiverem numa certa ordem. Se algum programa percorre a tabela, freqüentemente, numa certa ordem, pode ser interessante manter os registros armazenados nessa mesma ordem. Por exemplo, o comando alter table aluno order by nome 236 Capítulo 11: Criando, eliminando e modificando tabelas faz com os registros da tabela ALUNO sejam ordenados pelo atributo nome. O critério de ordena- ção pode conter mais de um atributo e ser direcionado pelas opções asc e desc. REMOVENDO TABELAS O comando drop table permite a remoção de mais de uma tabela. A sintaxe <comando drop table> : drop [ temporary ] table [ if exists ] <tabela>,... [ restrict | cascade ] admite ainda que seja especificado o tipo de comportamento em relação às chaves estrangeiras afetadas pela remoção dos registros. A opção if exists previne erros no caso das tabelas não mais existirem no banco de dados. Um comando como drop table aluno, professor cascade teria o efeito de remover todos os registros da tabela ALUNO (e, por efeito cascata,todos os regis- tros da tabela INSCRIÇÃO), todos os registros da tabela PROFESSOR (e, por efeito cascata, todos os registros da tabela DISCIPLINA). Ou seja, na nossa base exemplo, somente restariam os registros da tabela CURSO. ∴ Muito cuidado na utilização do efeito cascata. As conseqüências podem ser devastadoras e irremediáveis. INCLUSÃO, ALTERAÇÃO E REMOÇÃO DE COLUNAS MySQL permite que um mesmo comando alter table especifique mais de uma modificação na tabela. Pode-se, por exemplo, incluir uma nova coluna, e mudar o tipo de uma coluna já exis- tente. As cláusulas add column e modify column permitem inserir novas colunas e mudar as proprie- dades de uma coluna já existente na tabela sendo alterada. No exemplo abaixo, a tabela CURSO recebe uma nova coluna denominada numeracao e tem o tipo da coluna curso alterado. alter table curso add column numeracao int auto_increment unique, modify column curso varchar (50) not null Com a opção change column é possível renomear uma coluna além de alterar suas proprieda- des. Por exemplo, um comando como alter table curso change column curso nome_curso varchar (60) not null muda o nome da coluna curso para nome_curso e altera seu tipo. ∴ A opção ignore, mostrada na sintaxe do comando alter table, tem efeito quando uma modifica- ção na tabela pode causar erro pela violação de identificadores únicos. Como um comando alter table pode modificar a definição de chaves primárias e/ou candidatas, além de índices, é possí- vel que essas mudanças gerem conflitos entre os registros presentes na instância da tabela. O efeito da opção ignore é fazer com que alguns dos registros com identificadores duplicados sejam ignorados por ocasião da rearrumação da tabela. ∴ MySQL disponibiliza ainda uma cláusula que permite a alteração do valor default de uma colu- na. Note que quando o valor default é removido e a coluna não permite valores nulos, passam a valer as regras apresentadas na subseção Valores nulos. Um exemplo de utilização da cláusula alter column é o comando Criando, eliminando e modificando tabelas no Oracle 237 alter table inscricao alter column nota set default -1 que modifica o valor default da coluna nota da tabela INSCRIÇÃO para –1. 11.7 CRIANDO, ELIMINANDO E MODIFICANDO TABELAS NO ORACLE Neste livro, o termo banco de dados tem sido usado genericamente para definir um conjunto de tabelas relacionais e suas propriedades. Contudo, no âmbito do Oracle, esse termo tem um sen- tido um pouco diferente. Aqui, um banco de dados (database) é uma coleção de dados que são tratados como uma unidade. Um banco de dados possui elementos lógicos e físicos, de modo a facilitar a manipulação física sem interferência nas definições lógicas. ESTRUTURA DE UM BANCO DE DADOS ORACLE Os elementos lógicos e físicos de um banco de dados Oracle incluem: • tablespaces são unidades lógicas de armazenagem, que normalmente agrupam elementos afins, tais como as tabelas e os objetos que definem a estrutura de dados de um sistema de informação, ou aplicativo; um banco de dados pode estar di- vidido em uma ou mais tablespaces, que são constituídas fisicamente por um ou mais datafiles (descritos a seguir), explicitamente criados para suportar uma determinada tablespace. System tablespaces abrigam os objetos lógi- cos empregados pelo Oracle para gerenciar o banco de dados, tais como as tabelas do dicionário de dados e as definições correntes. Users tablespaces armazenam os dados dos objetos lógicos a elas associados. Cada tablespace tem uma certa capacidade de armazenagem, definida pela capacidade dos datafiles que a compõem. A capacidade de armazenagem total de um banco de dados é a soma das capacidades de suas tablespaces; • datafiles são estruturas físicas que contêm os dados armazenados no banco de dados, tais como as instâncias das tabelas e seus eventuais índices. Cada datafile está associado a apenas um banco de dados. Um ou mais datafiles formam as unidades lógicas de armazenagem denominadas tablespaces. Um datafile tem certas características a ele associadas, tais como as regras para expansão de sua capacidade quando do crescimento do banco de dados; • redo log files usualmente conhecidos como arquivos de log, são estruturas físicas que registram todas as alterações efetuadas sobre a instância de um banco de da- dos. No caso de falha no sistema ou nos dispositivos que armazenam um banco de dados, esses arquivos podem ser usados na recuperação dos dados que eventualmente tenham sido perdidos. Esse é um papel extremamente importante para a segurança e confiabilidade de um repositório de dados. Em certos casos, o próprio arquivo de log pode ser gerado de forma redun- dante, gravando-se simultaneamente duas ou mais cópias do mesmo (em di- ferentes dispositivos), reforçando-se ainda mais o grau de segurança. Cada banco de dados Oracle tem um conjunto de dois ou mais arquivos de log; • data blocks é a menor unidade de armazenagem de disco. Oracle lê e grava dados em blocos cujo tamanho padrão é definido pelo parâmetro DB_BLOCK_SIZE, mas permite a utilização de blocos com tamanhos diferentes do padrão. Tipica- mente, utiliza-se 4K ou 8K bytes; • extents refere-se à unidade de espaço em disco que é obtida numa alocação, que é expressa em termos de data blocks. Extents são definidos de acordo com a previsão inicial de espaço necessário e com a sua previsão de crescimento; 238 Capítulo 11: Criando, eliminando e modificando tabelas • segments é um conjunto de extents alocados para uma estrutura lógica que é armaze- nada. Um segment pode começar com um número inicial de extents e depois aumentar esse número. De acordo com a estrutura, há diferentes tipos de segments, entre eles: data segment − cada tabela tem um segment, onde os dados são armazenados. Tabelas particionadas usam um data segment para cada partição. Tabelas clusterizadas são armazenadas num único data seg- ment; index segment − cada índice é armazenado num único segment, exce- to para índices particionados que utilizam vários segments; rollbak segment − utilizados temporariamente nos processo de recuperação; temporary seg- ment − utilizados como área de trabalho na execução de comandos SQL; • control files todo banco de dados Oracle contém um arquivo de controle que contém seu nome, identificação de seus datafiles e redo log files e outras informações de controle. Esse arquivo é atualizado sempre que há alguma alteração na es- trutura de armazenagem do banco; • SGA é uma área de memória principal compartilhada que contém dados e outras informações para uma instância Oracle. Essa área é criada quando uma ins- tância é iniciada e permanece em uso até o fechamento da instância. Todos os usuários de uma instância Oracle compartilham essa área. A figura a seguir ilustra esses conceitos. SYSTEM TABLESPACES USERS TABLESPACES Datafile1 Datafile2 DatafileX1 DatafileX2 DatafileX3 DatafileX4ControlFile LogFileX2 LogFileX1 REDO LOG FILES Estrutura física de um banco de dados ORACLE Adicionalmente, o Oracle emprega a noção de esquema (schema), que é um coleção de objetos relacionais como tabelas, views, seqüências, stored procedures, índices, sinônimos, etc. Assim sendo, no Oracle o conceito de esquema é o que mais se aproxima do conceito de banco de da- dos deste livro. Um esquema pode ser imaginado como uma unidade lógica. Normalmente, um banco de dados Oracle comporta vários esquemas que podem estar relacionados ou não. Como é comum ter vários bancos de dados numa instalação, no Oracle é possível organizar cada um deles como um esquema. O gerenciador de banco de dados suporta todos esses esquemas simultaneamente. LIMITAÇÕES Uma tabela pode ter, no máximo, 1000 colunas. Criando, eliminando e modificando tabelas no Oracle 239 CRIANDO ESQUEMAS (SCHEMAS) Um esquema tem um nome que o identifica. Esquemas estão associados aos usuários
Compartilhar