Depois de muito tempo sem desenvolver nada muito complexo, que não passasse de pequenos scripts para gerenciamento de processos ou rotinas variadas no Linux, comecei a desenvolver um sistema em PHP para a minha empresa. Muito e muito tempo sem usar SQL, mas ainda com algumas lembranças de conceitos aprendidos na faculdade (tendo prática com MS SQL Server), com a cara e a coragem, fui à modelagem do banco de dados.

Criei algumas tabelas e seus campos (ou colunas) e comecei a relembrar dos conceitos de relacionamentos entre as tabelas (1:1, 1:N e M:N) e como usar o conceito de chaves estrangeiras para relacionar tabelas. Para quem ainda não estudou ou trabalhou com isso, o conceito é muito simples, vou tentar exemplificar usando o conceito de uma relação 1:N (um para N).

Digamos que temos uma tabela para cadastro de faturas de uma empresa composta basicamente pelo nome do cliente, data de emissão, data de vencimento, serviços prestados, valor unitário e valor total. Bom, imagine que a quantidade de serviços prestados e o valor unitário de cada um deles são variáveis para cada fatura, ou seja, em uma fatura você pode ter um serviço apenas ou vinte serviços distintos, sendo assim, como lidar com isso no banco de dados? Criar uma quantidade máxima possível de serviços para uma fatura e criar essa quantidade de campos na tabela do banco? Algo como servico_1, servico_2, …, servico_N? Bom, se você cadastrar apenas um serviço haverão N-1 registros nulos na sua tabela, sendo algo extremamente lusitano, hehehe.

Resolvemos isso da seguinte forma, pegamos os campos que não possuem quantidades variáveis (nome do cliente, data de emissão, data de vencimento e valor total) e colocamos em uma primeira tabela e pegamos os campos que possuem quantidades variáveis (serviços prestados e valor unitário) e colocamos em uma segunda tabela. Neste caso, a primeira tabela será a tabela pai e a segunda tabela será a tabela filha, onde para cada tabela pai, posso ter várias tabelas filhas, ou seja, para cada fatura, posso ter vários serviços (e respectivos valores), por isso este é um exemplo 1:N. Visualmente, ficaria algo como ilustra a figura a seguir:

Tabelas de exemplo.
Tabelas de exemplo.

Podemos notar que a tabela fatura_servicos possui uma chave primária a mais (fatura_id), o que significa que, além da sua própria chave primária (id), ela possui outra coluna onde ficará registrado o id da tabela pai a que ela pertence. Sendo assim, se eu tiver uma fatura com o id “1”, todas as faturas_servicos que tiverem o valor “1” na coluna fatura_id, serão pertencentes à fatura “1”, ou seja, esta chave identifica a qual tabela pai ela pertence.

Na prática seria algo como:

Tabela fatura:

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
nome_cliente varchar(45) NO (null) (null) (null)
data_emissao datetime NO (null) (null) (null)
data_vencimento datetime NO (null) (null) (null)
valor_total float NO (null) (null) (null)

Tabela fatura_servicos:

Field Type Null Key Default Extra
id int(10) unsigned NO PRI auto_increment
servicos varchar(45) NO (null) (null) (null)
valor_unitario float NO (null) (null) (null)
fatura_id int(10) unsigned NO PRI (null) (null)

Realizamos uma inserção na tabela fatura através do comando:

INSERT INTO fatura (nome_cliente, data_emissao, data_vencimento, valor_total) VALUES (‘Rafael Puga’, ’2009-01-01′, ’2009-01-06′, ’200,00′);

Pegamos o ID da ultima inserção feita no banco de dados e salvamos na variável @last;

SELECT @last := LAST_INSERT_ID();

Inserimos serviços na tabela “fatura_servicos”, registrando sua tabela pai com a variável @last:

INSERT INTO fatura_servicos (servicos, valor_unitario, fatura_id) VALUES (‘Manutencao de micro-computador’, ’50,00′, @last);

INSERT INTO fatura_servicos (servicos, valor_unitario, fatura_id) VALUES (‘Manutencao de notebook’, ’150,00′, @last);

Pronto, nossos dados foram inseridos no banco e já estão devidamente relacionados. Para constatar isso fazendo alguns SELECT’s:

SELECT * FROM fatura WHERE id=@last;

SELECT * FROM fatura_servicos WHERE fatura_id=@last;

Poderíamos usar um JOIN para relacionar os resultados de ambas as tabelas em apenas em uma consulta, porém não vou abordar isso no momento.

Como pôde ser visto, o uso de chaves estrangeiras é apenas um conceito que pode ser trabalhado sem necessidade de alguma configuração especifica na tabela, porém em alguns bancos de dados, como o MySQL, podemos dizer ao banco que aquilo é uma chave estrangeira, tendo como vantagem um controle maior do banco que vai ajudar a evitar inconsistência de dados. Por exemplo, neste caso, se criamos uma fatura e cadastramos três serviços nela e excluirmos a entrada na tabela registro (pai), teremos entradas órfãs em fatura_servicos, causando então a inconsistência de dados.

O que fazemos neste caso então é definir quem é a chave estrangeira para o banco de dados com a seguinte sintaxe:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, …)
REFERENCES tbl_name (index_col_name,…)
[ON DELETE
[ON UPDATE
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

É importante sabermos que o uso de FOREIGN KEYS só é suportado pelo engine InnoDB do MySQL (geralmente é usado o MyISAM por padrão, devendo ser explicitamente definido então). No caso dos bancos de dados utilizados aqui como exemplo, a criação das tabelas e chaves estrangeiras seria da seguinte forma:

– —————————————————–
– Tabela `mydb`.`fatura`
– —————————————————–
DROP TABLE IF EXISTS `mydb`.`fatura` ;
CREATE  TABLE IF NOT EXISTS `mydb`.`fatura` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nome_cliente` VARCHAR(45) NOT NULL ,
`data_emissao` DATETIME NOT NULL ,
`data_vencimento` DATETIME NOT NULL ,
`valor_total` FLOAT NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;

——————————————————-
– Tabela `mydb`.`fatura_servicos`
– —————————————————–
DROP TABLE IF EXISTS `mydb`.`fatura_servicos` ;
CREATE  TABLE IF NOT EXISTS `mydb`.`fatura_servicos` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`servicos` VARCHAR(45) NOT NULL ,
`valor_unitario` FLOAT NOT NULL ,
`fatura_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`id`, `fatura_id`) ,
INDEX `fk_fatura_servicos_fatura` (`fatura_id` ASC) ,
CONSTRAINT `fk_fatura_servicos_fatura`
FOREIGN KEY (`fatura_id` )
REFERENCES `mydb`.`fatura` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Note que no final das configurações dos campos da tabela, definimos o tipo no banco (InnoDB). Na tabela “fatura_servicos”, podemos ver a definição da chave estrangeira conforme a sintaxe padrão previamente comentada. Outra coisa importante de notarmos são as duas opções de ações no fim da criação “tabela_faturas”. Vemos as ações ON DELETE e ON UPDATE que podem ser configuradas com as opções RESTRICT, CASCADE, SET NULL, NO ACTION, que significam:

CASCADE: Se houver um DELETE da tabela pai, as tabelas filhas também serão deletadas, evitando inconsistência de dados. Se houver um UPDATE na chave primária da tabela pai, as chaves estrangeiras das tabelas filhas também serão atualizadas.

SET NULL: Caso uma a tabela pai seja deletada ou sua chave primária seja alterada, as tabelas filhas terão as suas chaves estrangeiras setadas para NULL (exceto quando forem criadas com o parâmetro NOT NULL).

NO ACTION: A tentativa de deletar ou atualizar uma tabela pai será ignorada caso ela tenha tabelas filhas.

RESTRICT: O mesmo que NO ACTION.

Apesar de a explicação ter sido baseada nas duas condições (DELETE e UPDATE), elas podem receber configurações individuais, como por exemplo, setar que ao deletar a ação será um CASCADE e ao atualizar um NO ACTION.

A partir de configurado, podemos usar normalmente as tabelas para fazer nossas query’s e mais tranquilos com a ajuda do próprio banco no gerenciamento dos relacionamentos. É basicamente isso, espero que seja útil a muitos!

Bom, por enquanto é só.

Atenciosamente,
Rafael Puga


Fontes:

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html

Orkut: MySQL – Brasil