MySQL:
Índices e Chaves Estrangeiras
Programação de Servidores
Marx Gomes Van der Linden
http://marx.vanderlinden.com.br/
Mecanismos de Armazenamento



MySQL suporta diversos mecanismos físicos
(engines) para o armazenamento de tabelas.
É possível ter, no mesmo banco de dados,
várias tabelas, cada uma com um mecanismo
diferente.
Para exibir todos os mecanismos instalados,
pode-se usar o comando SHOW ENGINES.
http://marx.vanderlinden.com.br/
2
Mecanismos de Armazenamento

Mecanimos otimizados em disco:
MyISAM (padrão)
 MERGE (tabelas MyISAM concatenadas)
 InnoDB
Outros:






MEMORY → Armazenamento em RAM.
BLACKHOLE → /dev/null.
ARCHIVE → Não permite modificações.
CSV → Arquivos de texto separados por vírgula.
http://marx.vanderlinden.com.br/
3
Mecanismos de Armazenamento


Cada banco de dados é armazenado em um
subdiretório no diretório de armazenamento de
MySQL (geralmente /var/lib/mysql/).
Cada tabela corresponde a:


Um arquivo .frm
Outros arquivos com o mesmo nome, e extensões
de acordo com o mecanismo.
http://marx.vanderlinden.com.br/
4
Mecanismos de Armazenamento

MyISAM → .MYD (dados), .MYI (índices)

MERGE → .MRG (tabelas MyISAM unidas)

InnoDB → .ibd (dados e índices)


ARCHIVE → .ARZ (dados), .ARM
(metadados)
CSV → .CSV (dados), .CSM (metadados)
http://marx.vanderlinden.com.br/
5
Mecanismos de Armazenamento

Para exibir os Mecanismos de Armazenamento
disponíveis, pode-se usar o comando SHOW
ENGINES.
http://marx.vanderlinden.com.br/
6
mysql> SHOW ENGINES\G
******************* 1. row *******************
Mecanismos
de Armazenamento
Engine:
MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with
great performance
******************* 2. row *******************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful
for temporary tables
******************* 3. row *******************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level
locking, and foreign keys
******************* 4. row *******************
Engine: BerkeleyDB
http://marx.vanderlinden.com.br/
7
Support: NO
Mecanismo de Armazenamento



Assim como qualquer outra propriedade da
tabela, o mecanismo de armazenamento pode
ser alterado após a criação da tabela.
ALTER TABLE nome ENGINE = InnoDB;
ALTER TABLE nome ENGINE = MyISAM;
http://marx.vanderlinden.com.br/
8
Índices


A criação de índices para campos de sua
tabela é o principal método de otimização de
acesso a dados fornecido por MySQL.
A busca por dados indexados pode ser muitas
ordens de magnitude mais rápidas que a busca
por dados não-indexados.
http://marx.vanderlinden.com.br/
9
Características dos Índices




Podem se referir a um campo ou a um conjunto
de campos.
Pode ser restrito a valores únicos ou permitir
repetições.
Pode haver mais de um índice na mesma
tabela.
Para strings, é possível indexar apenas os n
primeiros elementos.
http://marx.vanderlinden.com.br/
10
Criando uma tabela com índices



Para especificar os campos indexados na
criação da tabela, usa-se a palavra-chave
INDEX, dentro da definição dos campos da
tabela.
Sintaxe:
CREATE TABLE nome_tabela
(
nome TIPO,
nome TIPO, ...,
INDEX ( campos )
http://marx.vanderlinden.com.br/
)
11
Criando uma tabela com índices

Exemplo:
mysql> CREATE TABLE cd (
-> cd_id INT auto_increment PRIMARY KEY,
-> art_id INT NOT NULL,
-> titulo VARCHAR(255) NOT NULL,
-> lancamento DATE NOT NULL,
-> INDEX (cd_id)
-> );
Query OK, 0 rows affected (0.04 sec)
http://marx.vanderlinden.com.br/
12
Indexando um campo já existente



Para transformar em índice um campo já
existente, usa-se a sintaxe de ALTER TABLE.
Sintaxe:
ALTER TABLE tabela ADD INDEX
( campos )
http://marx.vanderlinden.com.br/
13
Tipos de Índices

Índice comum (INDEX)

Índice único (UNIQUE)*
Implica que não pode haver repetições na
coluna ou combinação de colunas.
Índice para busca de texto (FULLTEXT)


Suportado apenas em tabelas MyISAM
Chave primária (PRIMARY KEY)*


http://marx.vanderlinden.com.br/
14
Criando uma tabela com índices


Cada índice criado (menos a chave primária)
pode ter um nome.
CREATE TABLE nome_tabela
(
nome TIPO,
nome TIPO, ...,
INDEX [nome_indice] ( campos ),
UNIQUE [nome_indice] ( campos ),
PRIMARY KEY ( campos ),
FULLTEXT [nome_indice] ( campos )
);
http://marx.vanderlinden.com.br/
15
Adicionando índices à tabela




ALTER TABLE tabela ADD INDEX
[nome_indice] ( campos )
ALTER TABLE tabela ADD UNIQUE
[nome_indice] ( campos )
ALTER TABLE tabela ADD PRIMARY KEY
( campos )
ALTER TABLE tabela ADD FULLTEXT
[nome_indice] ( campos )
http://marx.vanderlinden.com.br/
16
Prefixos de Strings

Para indexar prefixos de campos de string,
basta adicionar (n) após o nome do campo,
onde n é o número de caracteres a indexar.
mysql> CREATE TABLE enderecos
-> (
->
nome
CHAR(30) NOT NULL,
->
endereco VARCHAR(60) NOT NULL,
->
INDEX (nome(10)),
->
INDEX (endereco(15))
-> );
Query OK, 0 rows affected (0.05 sec)
http://marx.vanderlinden.com.br/
17
Prefixos de Strings


O uso de prefixos na indexação é obrigatório
para índices de campos:
 BLOB
 TEXT
O índice FULLTEXT não pode ter prefixos
(ignorado, se especifcado)
http://marx.vanderlinden.com.br/
18
Removendo Índices


Para remover um índice, basta usar ALTER
TABLE ... DROP INDEX.
Sintaxe:
 ALTER TABLE tabela DROP INDEX
nome_indice;
 ALTER TABLE tabela DROP PRIMARY
KEY;
http://marx.vanderlinden.com.br/
19
Exibindo Índices


Para exibir os índices já existentes em uma
tabela, e seus respectivos nomes, usa-se SHOW
INDEX.
Sintaxe:
 SHOW INDEX FROM tabela;
http://marx.vanderlinden.com.br/
20
Tabela sem Índice
http://marx.vanderlinden.com.br/
21
Tabela com Índice
http://marx.vanderlinden.com.br/
22
Índices


O índice organiza as referências às linhas de
maneira ordenada
Busca mais eficiente

Algoritmos de posicionamento
http://marx.vanderlinden.com.br/
23
Usos de Índices em MySQL




Busca instantânea de valores mínimos e
máximos(MIN(), MAX())
Aceleração das ordenações e agrupamentos
(ORDER BY, GROUP BY)
Quando a busca só retorna colunas indexados,
apenas o índice precisa ser lido
Otimização de JOINs
http://marx.vanderlinden.com.br/
24
Índices em JOINs

Exemplo:
mysql> SELECT t1.i1, t2.i2, t3.i3
-> FROM t1 JOIN t2 JOIN t3
-> WHERE t1.i1 = t2.i2
->
AND t2.i2 = t3.i3;


Se cada tabela tem 1000 linhas:
Número de consultas: 1000 × 1000 × 1000 =
109
http://marx.vanderlinden.com.br/
25
Índices em JOINs

Utilizando índices:
1.Selecione a próxima linha em t1
2.Usando o índice, recupere a linha em t2 em
que a condição se verifica
3.Usando o índice e o valor anterior, recupere
a linha em t3 em que a condição se verifica
4.Volte à etapa 1
http://marx.vanderlinden.com.br/
26
Cache


O MySQL armazena temporariamente em
cache o resultado das últimas consultas
realizada.
Se mais de uma consulta idêntica for realizada
sem que a tabela tenha sido modificada,
MySQL consulta o cache e retorna o resultado
anterior
http://marx.vanderlinden.com.br/
27
Cache


Para limpar o cache, usa-se o comando FLUSH
TABLE.
Exemplo:
mysql> FLUSH TABLE teste1;
Query OK, 0 rows affected (0.00 sec)
http://marx.vanderlinden.com.br/
28
Uso de Índices

Índices devem ser colocados em campos
usados para busca, ordenamento e
agrupamento.

Não necessariamente as colunas que serão
exibidas ao usuário
http://marx.vanderlinden.com.br/
29
Uso de Índices

Exemplo:
 SELECT campoA
FROM tabela1 LEFT JOIN tabela2
ON tabela1.campoB =
tabela2.campoC
WHERE campoD = expr;
http://marx.vanderlinden.com.br/
30
Uso de Índices

Cardinalidade do campo → Número de valores
distintos que o campo pode conter.


Quanto maior a cardinalidade, mais
vantajoso será indexar o campo.
Número mágico: se um valor se repete em
ao menos 30% das linhas, o índice pode não
ser eficiente.
http://marx.vanderlinden.com.br/
31
Uso de Índices

Tamanho dos campos:

Quanto menor o espaço ocupado por um tipo
de dado, mais eficiente a indexação.
 Não usar BIGINT quando MEDIUMINT for
suficiente
 Não usar CHAR(100) se nunca ocorrem
valores com mais de 25 caracteres
http://marx.vanderlinden.com.br/
32
Uso de Índices

Para strings, na maioria dos casos, é vantajoso
usar um prefixo para strings.

Tamanho ideal do prefixo: mínimo possível
para garantir uma alta cardinalidade.
http://marx.vanderlinden.com.br/
33
Índices com múltiplos campos



Um índice que agrupa N campos funciona
como um conjunto de N índices diferentes,
cada um formando um conjunto com os X
primeiros campos indexados, para X ≤ N.
Exemplo:
INDEX(campoA, campoB, campoC)



→ INDEX(campoA, campoB, campoC)
→ INDEX(campoA, campoB)
→ INDEX(campoA)
http://marx.vanderlinden.com.br/
34
Desvantagens dos Índices

Índices só devem ser usados quando
necessário:


Requerem mais espaço em disco.
Tornam mais lentas as inserções,
exclusões e atualizações nos campos da
tabela.
http://marx.vanderlinden.com.br/
35
Indexação: Exemplo

Tabela com 5.000.000 de entradas:
mysql> SELECT * FROM teste1
-> WHERE codigo='6417484';
+---------+-----------------------+
| codigo | nome
|
+---------+-----------------------+
| 6417484 | Nina Genevieve Manuel |
+---------+-----------------------+
1 row in set (2.73 sec)
http://marx.vanderlinden.com.br/
36
Exemplo

Adicionando uma chave primária:
mysql> ALTER TABLE teste1
-> ADD PRIMARY KEY (codigo);
Query OK, 5000000 rows affected
(31 min 4.57 sec)
Records: 5000000 Duplicates: 0
http://marx.vanderlinden.com.br/
Warnings: 0
37
Exemplo

Após indexação:
mysql> SELECT * FROM teste1
-> WHERE codigo='6417484';
+---------+-----------------------+
| codigo | nome
|
+---------+-----------------------+
| 6417484 | Nina Genevieve Manuel |
+---------+-----------------------+
1 row in set (0.03 sec)
http://marx.vanderlinden.com.br/
38
Exemplo: Antes
mysql> SELECT * FROM teste1
-> WHERE codigo > 14999980;
+----------+-----------------------+
| codigo
| nome
|
+----------+-----------------------+
| 14999982 | Bolaven Katie Fiddler |
| 14999988 | Gorio Tatiana
|
| 14999981 | Nehemiah Neneng Aniya |
| 14999984 | Lomont Ruby Rolly
|
| 14999983 | Fitchie Ziller
|
| 14999997 | Kiera Emilia
|
| 14999994 | Ursula Heming
|
+----------+-----------------------+
http://marx.vanderlinden.com.br/
7 rows in set (2.19 sec)
39
Exemplo: Depois
mysql> SELECT * FROM teste1
-> WHERE codigo > 14999980;
+----------+-----------------------+
| codigo
| nome
|
+----------+-----------------------+
| 14999981 | Nehemiah Neneng Aniya |
| 14999982 | Bolaven Katie Fiddler |
| 14999983 | Fitchie Ziller
|
| 14999984 | Lomont Ruby Rolly
|
| 14999988 | Gorio Tatiana
|
| 14999994 | Ursula Heming
|
| 14999997 | Kiera Emilia
|
+----------+-----------------------+
http://marx.vanderlinden.com.br/
7 rows in set (0.28 sec)
40
Busca de Texto



O operador LIKE pode ser usado para fazer
buscas de padrões simples em texto.
Sintaxe:
expr [NOT] LIKE padrão

Caracteres especiais:
 % → Zero ou mais caracteres quaisquer
 _ → Exatamente um caractere qualquer
http://marx.vanderlinden.com.br/
41
Busca de Texto

O operador REGEXP funciona como LIKE, mas
usa a sintaxe de expressões regulares.



A expressão regular deve ser passada em
uma string, sem caracteres delimitadores
(//)
Sintaxe:
expr [NOT] REGEXP padrão
http://marx.vanderlinden.com.br/
42
Exemplo (sem índice)
mysql> SELECT * FROM teste1
-> WHERE nome = 'Gregory Lane';
+---------+--------------+
| codigo | nome
|
+---------+--------------+
| 1153016 | Gregory Lane |
+---------+--------------+
1 row in set (2.37 sec)
http://marx.vanderlinden.com.br/
43
Exemplo: LIKE (sem índice)
mysql> SELECT * FROM teste1
-> WHERE nome LIKE '%ory Lane';
+----------+--------------------+
| codigo
| nome
|
+----------+--------------------+
| 1153016 | Gregory Lane
|
| 1419658 | Ramon Cory Lane
|
| 8339502 | Wiley Mallory Lane |
| 14468906 | Mallory Lane
|
| 1457489 | Wall Cory Lane
|
| 12930229 | Cory Lane
|
+----------+--------------------+
http://marx.vanderlinden.com.br/
6 rows in set (2.70
sec)
44
Exemplo: REGEXP (sem índice)
mysql> SELECT * FROM teste1
-> WHERE nome REGEXP '.+ory Lane$';
+----------+--------------------+
| codigo
| nome
|
+----------+--------------------+
| 1153016 | Gregory Lane
|
| 1419658 | Ramon Cory Lane
|
| 8339502 | Wiley Mallory Lane |
| 14468906 | Mallory Lane
|
| 1457489 | Wall Cory Lane
|
| 12930229 | Cory Lane
|
+----------+--------------------+
http://marx.vanderlinden.com.br/
45
6 rows in set (19.11
sec)
Índice

Adicionando um índice:
mysql> ALTER TABLE teste1
-> ADD INDEX (nome);
Query OK, 5000000 rows affected (51.36 sec)
Records: 5000000 Duplicates: 0 Warnings: 0
http://marx.vanderlinden.com.br/
46
Exemplo (com índice)
mysql> SELECT * FROM teste1
-> WHERE nome = 'Gregory Lane';
+---------+--------------+
| codigo | nome
|
+---------+--------------+
| 1153016 | Gregory Lane |
+---------+--------------+
1 row in set (0.01 sec)

(Resultado anterior: 2.37 sec)
http://marx.vanderlinden.com.br/
47
Exemplo: LIKE (com índice)
mysql> SELECT * FROM teste1
-> WHERE nome LIKE '%ory Lane';
+----------+--------------------+
| codigo
| nome
|
+----------+--------------------+
| 1153016 | Gregory Lane
|
| 1419658 | Ramon Cory Lane
|
| 8339502 | Wiley Mallory Lane |
| 14468906 | Mallory Lane
|
| 1457489 | Wall Cory Lane
|
| 12930229 | Cory Lane
|
+----------+--------------------+
http://marx.vanderlinden.com.br/
6 rows in set (3.69
sec)
48
Exemplo: REGEXP (sem índice)
mysql> SELECT * FROM teste1
-> WHERE nome REGEXP '.+ory Lane$';
+----------+--------------------+
| codigo
| nome
|
+----------+--------------------+
| 1153016 | Gregory Lane
|
| 1419658 | Ramon Cory Lane
|
| 8339502 | Wiley Mallory Lane |
| 14468906 | Mallory Lane
|
| 1457489 | Wall Cory Lane
|
| 12930229 | Cory Lane
|
+----------+--------------------+
http://marx.vanderlinden.com.br/
49
6 rows in set (19.09
sec)
FULLTEXT

Adicionando um índice FULLTEXT:
mysql> ALTER TABLE teste1
-> ADD FULLTEXT (nome);
Query OK, 5000000 rows affected (2 min 56.35
sec)
Records: 5000000 Duplicates: 0 Warnings: 0

FULLTEXT só é suportado em campos CHAR,
VARCHAR e TEXT

Apenas em tabelas MyISAM
http://marx.vanderlinden.com.br/
50
Buscas com FULLTEXT

Colunas indexadas com FULLTEXT suportam 2
tipos de busca:


Linguagem Natural → Busca por linhas
contendo uma ou mais palavras
Modo Booleano → É possível estabelecer
requerimentos específicos para cada palavra
http://marx.vanderlinden.com.br/
51
Buscas com FULLTEXT


Uma busca com FULLTEXT procura por
palavras contidas em qualquer coluna
pertencente ao índice.
Para fazer buscas em um coluna específica, é
necessário criar um outro índice.
http://marx.vanderlinden.com.br/
52
Buscas com FULLTEXT

São ignoradas:



Palavras muito comuns na tabela (presentes em
50% das linhas ou mais).
Palavras pertencentes a uma lista de exclusão prédefinida.
Palavras menores que 4 caracteres.
http://marx.vanderlinden.com.br/
53
Busca por Linguagem Natural



A busca por linguagem natural procura por
colunas contendo qualquer palavra pertencente
à classe especificada, obedecendo às
restrições de FULLTEXT.
Sintaxe:
SELECT (...)
WHERE MATCH(campo)
AGAINST('string');
http://marx.vanderlinden.com.br/
54
Exemplo
mysql> SELECT * FROM teste1 WHERE
-> MATCH(nome) AGAINST('Cory Lane');
(...)
| 11544209 | Lane Reuben Amescua-Potts
|
| 7671508 | Robey Lane Fung-wong
|
| 8241234 | Lane Cheyenne Fung-wong
|
| 11792217 | Cory Calvin Calvin
|
| 12695524 | Dado Lane Dado
|
| 4598833 | Lane Mizer Mizer
|
+----------+-----------------------------+
14621 rows in set (0.11 sec)
http://marx.vanderlinden.com.br/
55
Exemplo

A consulta anterior é equivalente a:
mysql>
->
->
->
SELECT * FROM teste1 WHERE
MATCH(nome) AGAINST(
'Cory Lane'IN NATURAL LANGUAGE MODE
);
http://marx.vanderlinden.com.br/
56
Relevância



A expressão MATCH ... AGAINST também pode
ser colocada entre os campos a ser retornados.
Nesse caso, é retornada a relevância daquela
linha em relação à busca.
Quanto maior a relevância, maior a
semelhança entre a busca e o texto da linha.

Relevância = 0 → Nenhuma palavra
encontrada.
http://marx.vanderlinden.com.br/
57
Relevância

A relevância é calculada com base em:




Número de palavras na linha
Número de palavras únicas na linha
Número total de palavras na coleção
indexada
Número de linhas que contém cada palavra
buscada
http://marx.vanderlinden.com.br/
58
mysql> SELECT nome,
Relevância
-> MATCH(nome)
AGAINST('Cory Lane') AS rel
-> FROM teste1 WHERE MATCH(nome)
-> AGAINST('Cory Lane') > 0
-> ORDER BY rel DESC LIMIT 8;
+-----------------+-----------------+
| nome
| rel
|
+-----------------+-----------------+
| Cory Lane
| 12.863711357117 |
| Ramon Cory Lane | 12.720711708069 |
| Lane Cory Zion | 12.720711708069 |
| Dulce Lane Cory | 12.720711708069 |
| Wall Cory Lane | 12.720711708069 |
| Cory Lane Kama | 12.720711708069 |
| Cory Kade Cory | 8.4945526123047 |
| Lane Lane Gener | 7.6799483299255 |
+-----------------+-----------------+
http://marx.vanderlinden.com.br/
59
8 rows in set (0.08 sec)
Modo Booleano

O Modo Booleano fornece maior flexibilidade
na busca que a Linguagem Natural.
Todas as linhas são buscadas, ignorada a
regra dos 50%.
Para especificar o Modo Booleano, adiciona-se
IN BOOLEAN MODE antes do fechamento da
seção AGAINST.


http://marx.vanderlinden.com.br/
60
Modo Booleano

No Modo Booleano, usam-se as seguintes
regras na string:




"p1 p2 p3" → Procura a seqüência de
palavras, nessa ordem
+p4 → Retorna apenas linhas que contém a
palavra especificada
-p5 → Retorna apenas linhas que não
contém a palavra especificada
xy* → Retorna palavras que iniciam por xy
http://marx.vanderlinden.com.br/
61
Exemplo
mysql> SELECT * FROM teste1
-> WHERE MATCH(nome) AGAINST(
-> '"Cory Lane"' IN BOOLEAN MODE
-> );
+----------+-----------------+
| codigo
| nome
|
+----------+-----------------+
| 1419658 | Ramon Cory Lane |
| 1457489 | Wall Cory Lane |
| 11238001 | Cory Lane Kama |
| 12930229 | Cory Lane
|
+----------+-----------------+
http://marx.vanderlinden.com.br/
4 rows in set (0.09
sec)
62
Exemplo
mysql> SELECT * FROM teste1
-> WHERE MATCH(nome) AGAINST(
-> '"Cory Lane" -Wall'
-> IN BOOLEAN MODE
-> );
+----------+-----------------+
| codigo
| nome
|
+----------+-----------------+
| 1419658 | Ramon Cory Lane |
| 11238001 | Cory Lane Kama |
| 12930229 | Cory Lane
|
+----------+-----------------+
http://marx.vanderlinden.com.br/
3 rows in set (0.09
sec)
63
Exemplo
mysql>
->
->
->
SELECT * FROM teste1
WHERE MATCH(nome) AGAINST(
'+Mc* +Skyler' IN BOOLEAN MODE
);
(...)
| 6732237 | Skyler McDonald Keaton
|
| 5205268 | Barr Skyler Mckenzie
|
| 8595463 | Skyler McAfee Galman
|
|
391596 | McKeney Skyler
|
| 12329850 | Skyler McGinnis
|
| 8111897 | McCray Keli Skyler
|
| 7562360 | Skyler Celia Mckenzie
|
| 3335852 | Parry McCandless Skyler
|
+----------+------------------------------+
http://marx.vanderlinden.com.br/
156 rows in set (0.21 sec)
64
Considerações de eficiência

Sempre que possível, representar dados como
números ao invés de strings.
Comparações numéricas sempre são mais
eficientes que comparações com strings.
Usar sempre os menores tipos de dados
possíveis.


http://marx.vanderlinden.com.br/
65
Considerações de eficiência

Sempre que possível, usar colunas de tamanho
fixo.

CHAR ao invés de VARCHAR
Desvantagem: espaço.
Evitar colunas TEXT e BLOB


Colocar esses dados em uma tabela
separada.
Definir colunas como NOT NULL, se aplicável.



Utilizar ENUM.
http://marx.vanderlinden.com.br/
66
Integridade Referencial


O conceito de integridade referencial se
refere à manutenção automática da
consistência das referências cruzadas entre
diferentes tabelas.
Em SQL, a integridade referencial pode ser
atingida através do uso de chaves
estrangeiras.
http://marx.vanderlinden.com.br/
67
Exemplo
CD
Música
cd_id
titulo
artista
lancamento
gravadora
musica_id
cd_id
faixa
titulo
duracao
1
*
Tabela-pai
Tabela-filha
http://marx.vanderlinden.com.br/
68
Chaves estrangeiras

A indicação explícita de chaves estrangeiras
ajuda a manter a consistência em:
Inserção de novas linhas
 Exclusão de linhas
 Atualização de campos
Chaves estrangeiras só funcionam no
mecanismo InnoDB


http://marx.vanderlinden.com.br/
69
Chaves estrangeiras



Para especificar que um campo é uma chave
estrangeira, usa-se a seguinte sintaxe ao final
da creiação da tabela.
Sintaxe (simplificada):
FOREIGN KEY (colunas)
REFERENCES tabela (colunas)
[ON DELETE ação]
[ON UPDATE ação]
http://marx.vanderlinden.com.br/
70
Requisitos

Tanto a chave estrangeira quanto a chave
referenciada devem ser indexadas.
Índice criado automaticamente para a tabelafilha.
Ambas as chaves devem pertencer ao mesmo
tipo.


http://marx.vanderlinden.com.br/
71
Exemplo
mysql> CREATE TABLE musica (
-> musica_id INT auto_increment,
-> cd_id INT NOT NULL,
-> faixa INT NOT NULL,
-> titulo VARCHAR(255) NOT NULL,
-> duracao TIME NOT NULL,
-> PRIMARY KEY (musica_id),
-> FOREIGN KEY (cd_id)
->
REFERENCES cd (cd_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)
http://marx.vanderlinden.com.br/
72
Exemplo
mysql> INSERT INTO musica (cd_id, faixa,
-> titulo, duracao) VALUES
-> (5, 1, 'Seven Nation Army',
-> '00:03:51');
ERROR 1452 (23000): Cannot add or update a
child row: a foreign key constraint fails
(`music3/musica`, CONSTRAINT `musica_ibfk_1`
FOREIGN KEY (`cd_id`) REFERENCES `cd`
(`cd_id`))
http://marx.vanderlinden.com.br/
73
Adicionando uma chave estrangeira
mysql> CREATE TABLE musica (
->
musica_id INT NOT NULL
->
auto_increment,
->
cd_id INT NOT NULL,
->
faixa INT NOT NULL,
->
titulo VARCHAR(255) NOT NULL,
->
duracao TIME NOT NULL,
->
PRIMARY KEY (musica_id)
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE musica
-> ADD FOREIGN KEY (cd_id)
-> REFERENCES cd (cd_id);
Query OK, 0 rows affected
(0.15 sec)
http://marx.vanderlinden.com.br/
Records: 0 Duplicates: 0 Warnings: 0
74
Ações



É possível especificar ações específicas a ser
tomadas quando ocorrerem modificações ou
exclusões em uma campo indexado por chave
estrangeira.
Sintaxe:
FOREIGN KEY (colunas)
REFERENCES tabela (colunas)
[ON DELETE ação]
[ON UPDATE ação]
http://marx.vanderlinden.com.br/
75
Ações

NO ACTION
Alterações que causam inconsistências são
rejeitadas (padrão)
CASCADE


Se a linha da tabela-pai for
apagada/modificada, a linha correspondente
na tabela-filha também o será
SET NULL



Se a linha da tabela-pai for
apagada/modificada, a linha correspondente
76
na tabela-filhahttp://marx.vanderlinden.com.br/
receberá NULL