DesenvBD 02 SQL DDL

Texto

(1)Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Material de apoio – Notas de aula. Desenvolvimento em Banco de Dados Linguagem SQL Comandos DDL Sistemas de Informação. Uninove Prof. André Luís P. Santos andre.santos@uninove.br 2011 1. Convenções desta apresentação Siglas e nomenclaturas utilizadas neste material. Restrições de integridade (constraints): • PK = primary key (chave primária) • FK = foreign key (chave estrangeira) • UK = unique key (chave única / de unicidade) • CK = check constraint (regra de validação) • NN = not null (não nulo). 2. Uninove - Prof. André Santos.

(2) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Convenções desta apresentação Siglas e nomenclaturas utilizadas neste material. Tipos de dados (datatypes): • C = caractere (texto) • N = numérico (valor) • D = data (cronológica). 3. SGBD Relacionais - conceitos básicos. Chave primária — primary key (PK) • É o atributo, ou conjunto de atributos, que identifica univocamente cada linha da tabela. • Todos os campos que formam uma PK são obrigatórios, ou seja, não podem aceitar nulos (NOT NULL).. 4. Uninove - Prof. André Santos.

(3) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. SGBD Relacionais - conceitos básicos. Chave estrangeira — foreign key (FK) • É o atributo, ou conjunto de atributos, que faz o relacionamento com um registro de outra tabela (integridade referencial). • Aponta para a (ou faz referência a) chave primária da tabela relacionada.. 5. SGBD Relacionais - conceitos básicos Chave única — unique key (UK) • Define um atributo, ou conjunto de atributos, que não admite ocorrências repetidas nas linhas de uma tabela. • Porém não, necessariamente, formada por campos obrigatórios (ou seja, admite valor nulo). • Chaves do tipo unique, algumas vezes, são denominadas como “chaves secundárias”. • Pode ser chamada de “chave alternativa” (AK) — em especial quando também for definido not null. 6. Uninove - Prof. André Santos.

(4) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. SGBD Relacionais - conceitos básicos. Regra de validação — check constraint • É uma verificação para garantir a integridade de dados, conforme uma regra estabelecida. • Utiliza expressões lógicas (de forma semelhante ao comando “IF” das linguagens de programação).. 7. SGBD Relacionais - conceitos básicos. Não nulo — not null • Indica um tipo de check constraint para definir campos obrigatórios (mandatórios), isto é, que não podem aceitar o valor nulo. • O nulo indica um valor desconhecido ou não definido para o campo.. 8. Uninove - Prof. André Santos.

(5) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. SGBD Relacionais - conceitos básicos Tipos de dados utilizados nos modelos lógicos a serem apresentados — e sua correspondência no BD Oracle: • Caractere (C) – campos texto. No Oracle, geralmente, irão corresponder ao tipo VARCHAR (VARCHAR2) ou CHAR. • Numérico (N) – campos com valores numéricos. No Oracle, iremos utilizar o tipo NUMBER. • Data (D) – campos com data. No Oracle, correspondem ao tipo DATE (que inclui data e horário). 9. SGBD Relacionais - conceitos básicos Datatypes • Para os campos tipo VARCHAR e NUMBER, podese especificar o tamanho máximo do campo entre parênteses. • VARCHAR( t ) : onde t corresponde ao tamanho máximo em bytes (geralmente, correspondendo ao número de caracteres). Limite = 4000 bytes. • NUMBER( t , d ) : onde t corresponde ao número máximo de dígitos (precisão), sem considerar o ponto decimal, e d é o número máximo de casas decimais (escala). Limite = 38 dígitos. 10. Uninove - Prof. André Santos.

(6) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. SGBD Relacionais - conceitos básicos Datatypes • Colunas do tipo CHAR têm tamanho fixo. • CHAR( t ) : onde t corresponde ao tamanho fixo do campo, em bytes. • Caso um dado ocupe menos que o definido, será completado com espaços em branco à direita.. 11. SGBD Relacionais - conceitos básicos Datatypes • Na linguagem SQL do BD Oracle não há datatype do tipo “booleano” (verdadeiro ou falso).. • OBS.: Porém existe o tipo boolean na linguagem procedural suportada pelo Oracle (PL/SQL) — podendo assumir 3 estados (!): true (verdadeiro), false (falso) ou null (nulo).. 12. Uninove - Prof. André Santos.

(7) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. SGBD Relacionais - conceitos básicos Datatypes • O SGBD Oracle também oferece compatibilidade de sintaxe com datatypes ANSI SQL (que geralmente são convertidos para tipos de dados nativos do Oracle).. 13. SGBD Relacionais - conceitos básicos Datatypes de grande capacidade, no Oracle: • Texto: – LONG (antigo): até 2 GB – CLOB (a partir da versão 8i): até 4 GB. • Dados binários: – LONG RAW (antigo): até 2 GB – BLOB (a partir da versão 8i): até 4 G OBS.: O uso de LONG e LONG RAW não é mais recomendado.. Nota: “LOB” = Large Object 14. Uninove - Prof. André Santos.

(8) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Regras para nomes de objetos Regras para nomes de objetos no Oracle: •. Em geral (tabelas, colunas, etc.) devem ter de 1 a 30 caracteres. Exceções: – Databases: limitados a 8 caracteres – Database links (“db links”): podem ter até 128 caracteres. •. Devem começar com uma letra. – A menos que o nome esteja entre aspas — o que traria um trabalho extra na escrita dos comandos.. •. Podem conter letras, números e o caracter de sublinhado (“underline” ou “underscore”). –. O Oracle também aceita os caracteres $ e #, porém não recomenda que sejam utilizados pelos usuários (assim como letras acentuadas). – Objetos do tipo “database link” também podem conter pontos (.) e o caracter @ (arroba, ou “at” em inglês).. •. Normalmente, nomes são “case insensitive”, ou seja, o Oracle ignora a diferença entre minúsculas e maiúsculas nos nomes que identificam os objetos (sendo que os nomes são armazenados em maiúsculas no dicionário de dados).. •. Não podem coincidir com palavras reservadas do Oracle. 15. Modelo para estudo Para estudar os comandos SQL, inicialmente vamos nos basear no seguinte exemplo:. Moeda. 1. cota. n. Taxa Cambial. 16. Uninove - Prof. André Santos.

(9) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Modelo para estudo Vamos criar duas tabelas: uma contendo o cadastro das “moedas” e outra com as “taxas de conversão cambial” para a cotação diária das moedas. Tabela: MOEDA Campo. Tipo Tam. Dec. Obrig. PK UK FK Tab. Ref. Campo Ref.. CODIGO. C. 3. NOME. C. 30. SISCOMEX. N. 3. Validação Descrição Sigla da moeda. X. Nome da Moeda. X. Código da moeda no Siscomex. X. Tabela: TAXA_CAMBIAL Campo. Tipo Tam. Dec. Obrig. PK UK FK Tab. Ref. Campo Ref.. COD_MOEDA. C. 3. X. DATA_TAXA. D. -. X. VAL_TAXA. N. 12. 6. X MOEDA. X. Validação Descrição. COD_MOEDA. Sigla da moeda Data da cotação. >0. Valor da cotação (em R$). 17. Comandos SQL-DDL Para criar uma tabela, definindo as colunas de sua estrutura, utilizamos o comando CREATE TABLE:. CREATE TABLE moeda ( codigo VARCHAR2(3), nome VARCHAR2(30), siscomex NUMBER(3) );. 18. Uninove - Prof. André Santos.

(10) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Comandos SQL-DDL CREATE TABLE moeda ( codigo VARCHAR2(3) PRIMARY KEY, nome VARCHAR2(30) NOT NULL, siscomex NUMBER(3) UNIQUE );. 19. Comandos SQL-DDL CREATE TABLE moeda ( codigo VARCHAR(3) CONSTRAINT pk_moeda PRIMARY KEY, nome VARCHAR(30) CONSTRAINT nn_moeda_nome NOT NULL, siscomex NUMBER(3) CONSTRAINT uk_moeda UNIQUE );. 20. Uninove - Prof. André Santos.

(11) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Escrita de comandos SQL As quebras de linha, endentações e tabulações, em um típico comando SQL, podem ser feitas da forma mais conveniente, a fim de facilitar a leitura do comando. CREATE TABLE moeda ( codigo VARCHAR(3). CONSTRAINT pk_moeda PRIMARY KEY, nome VARCHAR(30) CONSTRAINT nn_moeda_nome NOT NULL, siscomex NUMBER(3) CONSTRAINT uk_moeda UNIQUE. );. OBS.: O caractere de ponto-e-vírgula (;) não faz parte da sintaxe do comando. Ele apenas marca o fim da instrução. 21. Adicionando constraints • Pode-se criar uma tabela e depois alterá-la adicionando as constraints (restrições de integridade). Exemplo: voltando à tabela básica... CREATE TABLE moeda ( codigo VARCHAR2(3), nome VARCHAR2(30), siscomex NUMBER(3) ); 22. Uninove - Prof. André Santos.

(12) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Adicionando constraints PK ALTER TABLE moeda ADD PRIMARY KEY (codigo); Ou melhor, nomeando a constraint.... ALTER TABLE moeda ADD CONSTRAINT pk_moeda PRIMARY KEY (codigo);. 23. Adicionando constraints UNIQUE ALTER TABLE moeda ADD UNIQUE (siscomex); Nomeando a constraint.... ALTER TABLE moeda ADD CONSTRAINT uk_moeda_siscomex UNIQUE (siscomex);. 24. Uninove - Prof. André Santos.

(13) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Adicionando constraints NOT NULL Obs.: Modificando a coluna nome.... ALTER TABLE moeda MODIFY nome NOT NULL; Nomeando a constraint.... ALTER TABLE moeda MODIFY nome CONSTRAINT nn_moeda_nome NOT NULL;. 25. Constraints PK e Unique geram índices • Constraints de chave primária (PK) ou unique (UK), automaticamente, criam índices para que o SGBD possa fazer a verificação (validação) de maneira eficiente. • Esses índices têm os mesmos nomes das respectivas constraints. OBS.: Posteriormente, a criação de “índices” será estudada com mais detalhes. 26. Uninove - Prof. André Santos.

(14) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Chaves compostas Não é possível definir mais de uma PK na tabela.. CREATE TABLE cod_moeda data_taxa val_taxa );. taxa_cambial ( VARCHAR2(3) PRIMARY KEY, DATE PRIMARY KEY, NUMBER(12,6),. 27. Chaves compostas • Para implementação de chaves compostas, ou qualquer constraint que referencie mais de uma coluna, é necessário defini-la fora da especificação da coluna.. 28. Uninove - Prof. André Santos.

(15) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Chave primária composta Criando a tabela com PK composta:. CREATE TABLE taxa_cambial ( A vírgula, indica o cod_moeda VARCHAR2(3), término da especificação da data_taxa DATE, coluna “val_taxa”. val_taxa NUMBER(12,6), CONSTRAINT pk_taxa PRIMARY KEY ( cod_moeda, data_taxa ) ); 29. Chave primária composta Adicionando a constraint de PK composta:. ALTER TABLE taxa_cambial ADD CONSTRAINT pk_taxa PRIMARY KEY ( cod_moeda, data_taxa );. 30. Uninove - Prof. André Santos.

(16) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Check constraint CREATE TABLE taxa_cambial ( cod_moeda VARCHAR2(3), data_taxa DATE, val_taxa NUMBER(12,6) CONSTRAINT nn_taxa_val NOT NULL CONSTRAINT ck_taxa_val CHECK ( val_taxa > 0 ), CONSTRAINT pk_taxa PRIMARY KEY ( cod_moeda, data_taxa ) );. 31. Adicionando uma check constraint Adicionando a check constraint:. ALTER TABLE taxa_cambial ADD CONSTRAINT ck_taxa_val CHECK ( val_taxa > 0 ) );. 32. Uninove - Prof. André Santos.

(17) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Check constraint – outros exemplos CREATE TABLE cliente ( codigo NUMBER PRIMARY KEY, nome VARCHAR(30) NOT NULL, sexo VARCHAR(1) NOT NULL CHECK (sexo = 'F' OR sexo = 'M') ); ou... CREATE TABLE cliente ( codigo NUMBER PRIMARY KEY, nome VARCHAR(30) NOT NULL, sexo VARCHAR(1) NOT NULL CHECK (sexo IN ('F','M')) ); 33. Check constraint – outros exemplos Caso a check constraint faça referência a mais de uma coluna, vale a mesma regra de chave composta, ou seja, deve ser definida separada da especificação de uma coluna específica. Por exemplo:. CREATE TABLE movimento ( codigo NUMBER PRIMARY KEY, dt_saida DATE NOT NULL, dt_retorno DATE, CONSTRAINT ck_movi_datas CHECK (dt_saida <= dt_retorno) ); 34. Uninove - Prof. André Santos.

(18) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Colunas com valores “default” • Exemplo: Supondo que, ao incluir um registro na tabela TAXA_CAMBIAL, caso não seja informada qual a moeda, seja assumido como valor default o código “USD” (dólar americano):. CREATE TABLE taxa_cambial ( cod_moeda VARCHAR2(3) DEFAULT 'USD', data_taxa DATE, val_taxa NUMBER(12,6) );. 35. Colunas com valores “default” • Também podemos modificar a coluna, para que seja definido um valor default para os próximos registros a serem incluídos na tabela:. ALTER TABLE taxa_cambial MODIFY cod_moeda DEFAULT 'USD';. 36. Uninove - Prof. André Santos.

(19) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. FK – chave estrangeira CREATE TABLE taxa_cambial ( cod_moeda VARCHAR(3) REFERENCES moeda, data_taxa DATE, val_taxa NUMBER(12,6), CONSTRAINT pk_taxa PRIMARY KEY ( cod_moeda, data_taxa ) );. 37. FK – chave estrangeira Nomeando a constraint... CREATE TABLE taxa_cambial ( cod_moeda VARCHAR(3) CONSTRAINT fk_moeda_taxa REFERENCES moeda, data_taxa DATE, val_taxa NUMBER(12,6), CONSTRAINT pk_taxa PRIMARY KEY ( cod_moeda, data_taxa ) ); 38. Uninove - Prof. André Santos.

(20) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Adicionando uma constraint de FK Adicionando a constraint de chave estrangeira (integridade referencial): ALTER TABLE taxa_cambial ADD FOREIGN KEY (cod_moeda) REFERENCES moeda; Nomeando a constraint... ALTER TABLE taxa_cambial ADD CONSTRAINT fk_taxa_moeda FOREIGN KEY (cod_moeda) REFERENCES moeda; 39. Indicação opcional do campo da PK • Opcionalmente pode-se indicar qual a coluna (ou quais colunas) da PK da tabela referenciada. • Por default, será considerada aquela definida pela constraint de chave primária na tabela referenciada (tabela “pai”).. ALTER TABLE taxa_cambial ADD FOREIGN KEY (cod_moeda) REFERENCES moeda (codigo); não necessário 40. Uninove - Prof. André Santos.

(21) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. FK com exclusão em cascata • Exclusão em cascata é uma forma alternativa de manter a integridade referencial. • Caso seja excluído um registro da tabela “pai”, todos os registros da tabela “filho”, que estiverem relacionados, também serão excluídos automaticamente!. ALTER TABLE taxa_cambial ADD FOREIGN KEY (cod_moeda) REFERENCES moeda ON DELETE CASCADE; 41. Alteração ou exclusão de constraints • Desabilitando uma constraint:. ALTER TABLE taxa_cambial DISABLE CONSTRAINT fk_taxa_moeda; • Habilitando / reabilitando uma constraint:. ALTER TABLE taxa_cambial ENABLE CONSTRAINT fk_taxa_moeda; • Eliminando uma constraint:. ALTER TABLE taxa_cambial DROP CONSTRAINT fk_taxa_moeda; 42. Uninove - Prof. André Santos.

(22) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Eliminando uma constraint “not null” • Da mesma forma que a definição de constraint “not null” costuma ser um caso especial, a eliminação também segue o mesmo padrão:. ALTER TABLE moeda MODIFY nome NULL; nome da coluna. • Porém nada impediria que fosse feito (mas não usual):. ALTER TABLE moeda DROP CONSTRAINT nn_moeda_nome; nome da constraint 43. Modificando uma coluna Utilizando o comando ALTER TABLE com a cláusula MODIFY, podemos modificar várias definições de uma coluna: tipo, tamanho, obrigatoriedade (not null) e valor default. Exemplos: • Aumentando o tamanho de uma coluna:. ALTER TABLE moeda MODIFY nome VARCHAR(50); • Modificando o tipo de uma coluna:. ALTER TABLE cliente MODIFY sexo CHAR(1); 44. Uninove - Prof. André Santos.

(23) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Modificando datatype - observações • Para modificar o datatype de uma coluna, ela deve estar vazia (todos os registros sem valor nesta coluna) ou deve haver compatibilidade entre o tipo atual e o novo. – Exemplo 1: Ao tentar alterar uma coluna do tipo VARCHAR para NUMBER (ou vice-versa), caso haja dados, resultará em erro. – Exemplo 2: Mudar o tipo de dados de VARCHAR para CHAR é possível, mesmo que já existam dados.. 45. Modificando tamanho - observações • Sempre é possível aumentar o tamanho de uma coluna (dentro do limite do datatype), porém geralmente não é possível diminuir seu tamanho caso a coluna já contenha dados. • Um coluna do tipo VARCHAR, pode ser diminuída até o tamanho do maior dado existente nela. • Colunas do tipo NUMBER ou CHAR não podem ser diminuídas se contiverem dados.. 46. Uninove - Prof. André Santos.

(24) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Acrescentando colunas • Uma coluna (parênteses não são necessários):. ALTER TABLE moeda ADD obs VARCHAR(50); • Ou várias colunas (neste caso, os parênteses são obrigatórios após a cláusula ADD):. ALTER TABLE moeda ADD ( obs1 VARCHAR(50), obs2 VARCHAR(50) ); 47. Acrescentando colunas - observações • Podem ser acrescentadas colunas já com suas respectivas constraints (como na criação das tabelas). • Porém, se a tabela contiver registros, um problema comum é não conseguir adicionar uma coluna com NOT NULL — é necessário primeiro adicionar a coluna, depois “popular” a coluna e, finalmente, alterá-la para NOT NULL (adicionar a constraint).. 48. Uninove - Prof. André Santos.

(25) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Eliminando colunas • Sintaxe específica para eliminar apenas uma coluna, utilizando a palavra reservada COLUMN:. ALTER TABLE moeda DROP COLUMN obs; • Para eliminar uma ou várias colunas (utilizando parênteses após a cláusula DROP e sem a palavra reservada COLUMN):. ALTER TABLE moeda DROP ( obs1, obs2 ); •. OBS.: Recursos suportados somente a partir da versão 8i (8.1.5) do Oracle. 49. Renomeando colunas. ALTER TABLE moeda RENAME COLUMN obs TO observacao; nome atual. novo nome. • OBS.: Recurso suportado somente a partir da versão 9i release 2 (9.2) do Oracle. 50. Uninove - Prof. André Santos.

(26) Desenv. Banco de Dados: Linguagem SQL - comandos DDL. Renomeando tabelas. RENAME taxa_cambial TO cotacao; nome atual. novo nome. 51. Uninove - Prof. André Santos.

(27)