Usando comandos ALTER TABLE, UPDATE e DELETE
Neste artigo, vamos entender como funcionam os comandos ALTER TABLE, DROP, DELETE e UPDATE. Consideremos as seguintes tabelas com seus campos e registros:
create table vendedor (
codigo_vendedor int not null primary key,
nome varchar (50),
idade char (3),
sexo char (1),
salario decimal (10,2)
)
create table cliente (
codigo_cliente int not null primary key,
nome varchar (50),
cidade varchar (10),
tipo_industria char (1)
)
create table pedido (
numero_pedido int not null primary key,
codigo_cliente int,
codigo_vendedor int,
valor_pedido decimal (10,2)
)
alter table pedido
add constraint fk_pedido_cliente foreign key (codigo_cliente) references cliente (codigo_cliente) on update cascade on delete cascade
alter table pedido
add constraint fk_pedido_vendedor foreign key (codigo_vendedor) references vendedor (codigo_vendedor) on update cascade on delete cascade
Os comandos ON UPDATE CASCADE e ON DELETE CASCADE servem para que, no momento que eu for utilizar meus comandos UPDATE ou DELETE para modificar os campos ou registros da minha tabela, todos as PK e FK sejam modificadas, também.
- Cliente
http://andrielleazevedo.files.wordpress.com/2011/06/select-12.jpg?w=604
- Pedido
http://andrielleazevedo.files.wordpress.com/2011/06/select-21.jpg?w=604
- Vendedor
http://andrielleazevedo.files.wordpress.com/2011/06/select-33.jpg?w=604
Observe a tabela VENDEDOR. Suponhamos que preciso inserir mais quatro campos: e-mail, endereço, cidade e CEP. Eu não preciso excluir minha tabela VENDEDOR e criá-la novamente. O SQL oferece um comando onde posso alterar essa tabela: alter table.
Entro com o comando ALTER TABLE e o nome da tabela que será alterada, a cláusula ADD (adicionar) e o nome do novo campo e o tipo do dado.
Depois de inseridos meus novos campos, veja como ficou a tabela:
alter table vendedor
add email varchar (20);
alter table vendedor
add endereco varchar (40);
alter table vendedor
add cidade varchar (15);
alter table vendedor
add cep varchar (9);
http://andrielleazevedo.files.wordpress.com/2011/06/select-41.jpg?w=604&h=103
Observação: os novos campos adicionados são nulos porque não foi adicionado nenhum registro.
Observe, agora, a tabela CLIENTE. Para excluir registros de alguma tabela especificada, podemos utilizar o comando DELETE junto com a cláusula WHERE. Se a cláusula WHERE estiver ausente, todos os registros da tabela especificada serão excluídos. Resultado: tabela válida, mas vazia.
Suponhamos que eu queira remover o cliente chamado Estrutura Construtora da minha tabela CLIENTE, que é a tabela onde são inseridos os meus clientes. Então, entramos com o comando DELETE e a tabela especificada, juntamente com a cláusula WHERE e o campo da tabela que será declarado o registro que será excluído.
Lembrem-se: a cláusula WHERE é uma condição. Se não utilizá-la para especificar o registro que será excluído, todos os campos da tabela CLIENTE serão apagados.
delete from cliente where codigo_cliente = 4;
Observação: se no momento em que foram criados os relacionamentos de foreign keys da minha base de dados eu não tiver usado o ON UPDATE CASCADE, teríamos o seguinte erro:
http://andrielleazevedo.files.wordpress.com/2011/06/mensagem-erro-1.jpg?w=604
Este erro que diz que o comando DELETE conflitou com a constraint fk_pedido_cliente criada para referenciar uma chave estrangeira entre a tabela PEDIDO e CLIENTE e, ainda especifica o nome da base de dados onde ocorre o erro, o nome da tabela e seu campo.
Esse erro aconteceu porque a tabela PEDIDO (CODIGO_CLIENTE) referencia CLIENTE (CODIGO_CLIENTE), ou seja, uma foreign key. Conclusão: utilize ON UPDATE CASCADE nos seus relacionamentos.
Ainda na tabela CLIENTE, vamos alterar o campo tipo_industria. Todos as linhas que tem tipo_industria como B deverão ser trocados para C. Como fazer isso? Temos um comando chamado UPDATE que muda os valores dos campos especificados em todas as linhas que satisfazem a condição. Podemos utilizá-lo com a cláusula SET, onde são especificadas as colunas que serão modificadas. As demais permanecem com seus valores atuais.
update cliente set tipo_industria='C' where tipo_industria='B';
Dando um novo select na tabela CLIENTE, veja como ela ficou depois de excluirmos um cliente e fazendo os updates do tipo_industria:
http://andrielleazevedo.files.wordpress.com/2011/06/select-51.jpg?w=604
Agora observe a tabela VENDEDOR. Vamos mudar o nome do vendedor Abel para Abel Medeiros. Para isso, utilizamos o comando UPDATE.
update vendedor set nome=’Abel Medeiros’ where nome=’Abel’;
update vendedor set codigo_vendedor=777 where codigo_vendedor=111;
Veja como ficou nossa tabela VENDEDOR depois que fizemos os updates para o nome Abel Medeiros e trocamos seu codigo_vendedor de 111 para 777.
http://andrielleazevedo.files.wordpress.com/2011/06/select-61.jpg?w=604&h=103
Note também que, na tabela CLIENTE onde tenho um relacionamento com a tabela VENDEDOR (codigo_vendedor), tem-se os dados atualizados.
http://andrielleazevedo.files.wordpress.com/2011/06/select-71.jpg?w=604
Para finalizar, recomendo a leitura do artigo Restrições de integridade referencial em cascata.