5. Relações entre tabelas

5.1. Chaves estrangeiras

Um banco de dados relacional é um conjunto de tabelas interligadas por relações. Vejamos um exemplo inspirado na tabela [BIBLIO] anterior, cuja estrutura era a seguinte:

Um exemplo de conteúdo era o seguinte:

Podemos querer informações sobre os diferentes autores dessas obras, por exemplo, seus nom e prénom, sua data de nascimento, seu nationalité. Vamos criar uma tabela desse tipo. Clique com o botão direito do mouse em [DBBIBLIO / Tables] e selecione a opção [New Table]:

Vamos agora criar a seguinte tabela [AUTEURS]:

`id`	chave primária da tabela — serve para identificar uma linha de forma exclusiva
`nom`	nome do autor
`prénom`	nome do autor, caso haja
`date_naissance`	sua data de nascimento
`nationalite`	país de origem

O conteúdo da tabela [AUTEURS] poderia ser o seguinte:

Voltemos à tabela [BIBLIO] e ao seu conteúdo:

Na coluna [AUTEUR] da tabela, não é mais necessário inserir o nome do autor. É preferível inserir o número (id) que ele possui na tabela [AUTEURS]. Vamos, portanto, criar uma nova tabela chamada [LIVRES]. Para criá-la, vamos usar o script [biblio.sql] criado no parágrafo 3.14. Carregamos esse script com a ferramenta [Script Executive, Ctrl-F12]:

Modificamos o script de criação da tabela BIBLIO para adaptá-lo ao da tabela LIVRES:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Restrições de exclusividade ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Chaves primárias ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

Comentamos apenas as alterações:

linha 4: o campo [AUTEUR] da tabela passa a ser um número inteiro. Esse número faz referência a um dos autores da tabela [AUTEURS] criada anteriormente.
linhas 11-19: os nomes dos autores foram substituídos por seus números de autor.
linha 29: o nome da restrição foi alterado. Anteriormente, ela se chamava [ UNQ1_BIBLIO ]. Agora, ela se chama [ UNQ1_LIVRES ]. Esse nome pode ser qualquer um. No entanto, é preferível que tenha um significado. Neste caso, esse esforço não foi feito. As restrições nos diferentes campos e nas diferentes tabelas de um banco de dados devem ser diferenciadas por nomes distintos. Vale lembrar que a restrição da linha 29 exige que um título seja único na tabela.
linha 36: alteração do nome da restrição na chave primária ID.

Vamos executar este script. Se for bem-sucedido, obteremos a seguinte nova tabela [LIVRES]:

Podemos nos perguntar se, no fim das contas, saímos ganhando com essa mudança. De fato, a tabela [LIVRES] apresenta números de autores em vez de seus nomes. Como há milhares de autores, parece difícil estabelecer a ligação entre um livro e seu autor. Felizmente, a linguagem SQL está aqui para nos ajudar. Ela nos permite consultar várias tabelas ao mesmo tempo. A título de exemplo, apresentamos a consulta SQL, que nos permite obter os títulos dos livros da biblioteca, associados às informações de seus autores. Vamos usar o editor SQL (F12) para emitir o comando SQL a seguir:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

Ainda é cedo para explicar essa ordem SQL. Voltaremos a esse assunto em breve. O resultado dessa consulta é o seguinte:

Cada livro foi associado corretamente ao seu autor e às informações relacionadas a ele.

Vamos resumir o que acabamos de fazer:

temos duas tabelas que reúnem informações de natureza diferente:
- a tabela AUTEURS reúne informações sobre os autores
- a tabela LIVRES reúne informações sobre os livros adquiridos pela biblioteca
essas tabelas estão interligadas. Um livro tem, necessariamente, um autor. Pode até ter vários. Esse caso não foi considerado aqui. A coluna [AUTEUR] da tabela [LIVRES] faz referência a uma linha da tabela [AUTEURS]. Isso é chamado de relação.

A relação que vincula a tabela [LIVRES] à tabela [AUTEURS] é, na verdade, uma forma de restrição: uma linha da tabela [LIVRES] deve sempre ter um número de autor que exista na tabela [AUTEURS]. Se uma linha da tabela [LIVRES] tivesse um número de autor que não existisse na tabela [AUTEURS], estaríamos em uma situação anômala em que não seria possível identificar o autor de um livro.

A tabela SGBD é capaz de verificar se essa restrição está sempre sendo cumprida. Para isso, vamos adicionar uma restrição à tabela [LIVRES]:

A relação que une a coluna [AUTEUR] da tabela [LIVRES] ao campo [ID] da tabela [AUTEURS] é chamada de relação de chave estrangeira. O campo [AUTEUR] da tabela [LIVRES] é denominado “chave estrangeira” ou “foreign key” no assistente acima. Definir uma chave estrangeira significa que o valor de uma coluna [c1] de uma tabela [T1] deve existir na coluna [c2] da tabela [T2]. A coluna [c1] é chamada de “chave estrangeira” da tabela T1 em relação à coluna [c2] da tabela [T2]. A coluna [c2] costuma ser a chave primária da tabela [T2], mas isso não é obrigatório.

Definimos a chave estrangeira [AUTEUR] da tabela [LIVRES] com base no campo [ID] da tabela [AUTEURS] da seguinte maneira:

nome da restrição: livre
coluna “chave estrangeira”, neste caso a coluna [AUTEUR] da tabela [LIVRES]
tabela referenciada pela chave estrangeira. Aqui, a coluna [AUTEUR] da tabela [LIVRES] deve ter um valor na coluna [ID] da tabela [AUTEURS]. Portanto, é a tabela [AUTEURS] que é referenciada.
Coluna referenciada pela chave estrangeira. Neste caso, a coluna [ID] da tabela [AUTEURS].

Validamos essa restrição:

Se tudo correr bem, ela será aceita:

Qual é a consequência dessa nova restrição de chave estrangeira? Com o editor SQL (F12), vamos tentar inserir uma linha na tabela LIVRES com um número de autor inexistente:

A operação [INSERT] acima tentou inserir um livro com um número de autor (100) inexistente. A execução da consulta falhou. A mensagem de erro associada indica que houve violação da restrição de chave estrangeira “FK_LIVRES_AUTEURS”. É a que acabamos de definir.

5.2. Operações de junção entre duas tabelas

Ainda no banco de dados [DBBIBLIO] (ou em qualquer outro banco de dados), vamos criar duas tabelas de teste chamadas TA e TB, definidas da seguinte forma:

Tabela TA

- ID: chave primária da tabela TA

- DATA: um dado qualquer

Tabela TB

- ID: chave primária da tabela TB

- IDTA: chave estrangeira da tabela TB que faz referência à coluna ID da tabela TA. Assim, um valor da coluna IDTA da tabela TA deve existir na coluna ID da tabela TA

- VALEUR: qualquer dado

No editor SQL (F12), vamos emitir comandos SQL que utilizam simultaneamente as duas tabelas TA e TB.

SQL>select * from TA,  TB

A ordem SQL utiliza, após a palavra-chave FROM, as duas tabelas TA e TB. A operação FROM TA, TB provocará a criação temporária de uma nova tabela na qual cada linha da tabela TA será associada a cada uma das linhas da tabela TB. Assim, se a tabela TA tiver NA linhas e a tabela TB tiver NB linhas, a tabela resultante terá NA × NB linhas. É isso que mostra a captura de tela acima. Além disso, cada linha contém as colunas das duas tabelas. As colunas coli especificadas na ordem [SELECT col1, col2, ... FROM ...] indicam aquelas que devem ser mantidas. Aqui, a palavra-chave * indica que todas as colunas da tabela resultante são solicitadas. Às vezes, diz-se que a tabela resultante da ordem anterior SQL é o produto cartesiano das tabelas TA e TB.

Acima, cada linha da tabela TA foi associada a cada linha da tabela TB. Em geral, deseja-se associar a uma linha da tabela TA as linhas da tabela TB que tenham uma relação com ela. Essa relação geralmente assume a forma de uma restrição de chave estrangeira. É o caso aqui. A uma linha da tabela TA, é possível associar as linhas da tabela TB que satisfazem a relação TB.IDTA=TA.ID. Há várias maneiras de fazer essa consulta:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

A ordem SQL é análoga à anterior, mas apresenta duas diferenças:

as linhas resultantes do produto cartesiano TA x TB são filtradas por uma cláusula WHERE que associa a uma linha da tabela TA, apenas as linhas da tabela TB que satisfazem a relação TB.IDTA=TA.ID
solicita-se apenas determinadas colunas com a sintaxe [T.col], em que T é o nome de uma tabela e col é o nome de uma coluna dessa tabela. Essa sintaxe permite eliminar a ambiguidade que poderia surgir caso duas tabelas tivessem colunas com o mesmo nome. Quando essa ambiguidade não existe, pode-se usar a sintaxe [col] sem especificar a tabela dessa coluna.

O resultado obtido é o seguinte:

O mesmo resultado pode ser obtido com a seguinte ordem SQL:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

Do termo [inner join] deriva o nome “junção interna”, dado a esse tipo de operação entre duas tabelas. Veremos que existe também uma “junção externa”. Em uma junção interna, a ordem das tabelas na consulta não afeta o resultado: FROM TA inner join TB é equivalente a FROM TB inner join TA.

A instrução SQL anterior inclui na tabela resultante apenas as linhas da tabela TA referenciadas por pelo menos uma linha da tabela TB. Assim, a linha de TA [3, data3] não aparece no resultado, pois não é referenciada por uma linha de TB. Pode-se querer todas as linhas de TA, independentemente de serem ou não referenciadas por uma linha de TB. Nesse caso, utiliza-se uma junção externa entre as duas tabelas:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

Temos aqui uma junção externa à esquerda (“left outer join”). Para entender o termo “FROM TA left outer join TB”, é preciso imaginar uma junção com a tabela TA à esquerda e a tabela TB à direita. Todas as linhas da tabela à esquerda aparecem no resultado de uma junção externa à esquerda, mesmo aquelas para as quais a relação de junção não é verificada. Essa relação de junção não é necessariamente uma restrição de chave estrangeira, embora esse seja, no entanto, o caso mais comum.

Na seguinte ordem:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

é a tabela TB que está à “esquerda” na junção externa. Portanto, todas as linhas de TB aparecerão no resultado:

Ao contrário da junção interna, a ordem das tabelas é, portanto, importante. Também existem junções externas direitas:

FROM TA left outer join TB é equivalente a FROM TB right outer join TA: a tabela TA está à esquerda
FROM TB com left outer join em TA é equivalente a FROM TA com right outer join em TB: a tabela TB está à esquerda

Agora que já conhecemos os fundamentos da exploração simultânea de várias tabelas, podemos abordar operações de consulta mais complexas em bancos de dados.