PostgreSQL Prático/Apêndices/Integridade Referencial - PostgreSQL

15.3 - Integridade Referencial - Postgresql

Tradução livre do documentação "CBT Integrity Referential":

http://techdocs.postgresql.org/college/002_referentialintegrity/.

Integridade Referencial (relacionamento) é onde uma informação em uma tabela se refere à informações em outra tabela e o banco de dados reforça a integridade.

Tabela1 ------------> Tabela2

Onde é Utilizado?

Onde pelo menos em uma tabela precisa se referir para informações em outra tabela e ambas precisam ter seus dados sincronizados.

Exemplo: uma tabela com uma lista de clientes e outra tabela com uma lista dos pedidos efetuados por eles.

Com integridade referencial devidamente implantada nestas tabelas, o banco irá garantir que você nunca irá cadastrar um pedido na tabela pedidos de um cliente que não exista na tabela clientes.

O banco pode ser instruído para automaticamente atualizar ou excluir entradas nas tabelas quando necessário.

Primary Key (Chave Primária) - é o campo de uma tabela criado para que as outras tabelas relacionadas se refiram a ela por este campo. Impede mais de um registro com valores iguais. É a combinação interna de UNIQUE e NOT NULL.

Qualquer campo em outra tabela do banco pode se referir ao campo chave primária, desde que tenham o mesmo tipo de dados e tamanho da chave primária.

Exemplo:

clientes (codigo INTEGER, nome_cliente VARCHAR(60))

codigo nome_cliente

1 PostgreSQL inc.

2 RedHat inc.

pedidos (relaciona-se à Clientes pelo campo cod_cliente)

cod_pedido cod_cliente descricao

Caso tentemos cadastrar um pedido com cod_cliente 2 ele será aceito.

Mas caso tentemos cadastrar um pedido com cod_cliente 3 ele será recusado pelo banco.

Criando uma Chave Primária

Deve ser criada quando da criação da tabela, para garantir valores exclusivos no campo.

CREATE TABLE clientes(cod_cliente BIGINT, nome_cliente VARCHAR(60) PRIMARY KEY (cod_cliente));

Criando uma Chave Estrangeira (Foreign Keys)

É o campo de uma tabela que se refere ao campo Primary Key de outra.

O campo pedidos.cod_cliente refere-se ao campo clientes.codigo, então pedidos.cod_cliente é uma chave estrangeira, que é o campo que liga esta tabela a uma outra.

CREATE TABLE pedidos(

cod_pedido BIGINT,

cod_cliente BIGINT REFERENCES clientes,

descricao VARCHAR(60)

);

Outro exemplo:

FOREIGN KEY (campoa, campob)

REFERENCES tabela1 (campoa, campob)

ON UPDATE CASCADE

ON DELETE CASCADE);

Cuidado com exclusão em cascata. Somente utilize com certeza do que faz.

Dica: Caso desejemos fazer o relacionamento com um campo que não seja a chave primária, devemos passar este campo entre parênteses após o nome da tabela e o mesmo deve obrigatoriamente ser UNIQUE.

...

cod_cliente BIGINT REFERENCES clientes(nomecampo),

...

Parâmetros Opcionais: ON UPDATE parametro e ON DELETE parametro.

ON UPDATE paramentros:

NO ACTION (RESTRICT) - quando o campo chave primária está para ser atualizado a atualização é abortada caso um registro em uma tabela referenciada tenha um valor mais antigo. Este parâmetro é o default quando esta cláusula não recebe nenhum parâmetro.

Exemplo: ERRO Ao tentar usar "UPDATE clientes SET codigo = 5 WHERE codigo = 2. Ele vai tentar atualizar o código para 5 mas como em pedidos existem registros do cliente 2 haverá o erro.

CASCADE (Em Cascata) - Quando o campo da chave primária é atualizado, registros na tabela referenciada são atualizados.

Exemplo: Funciona: Ao tentar usar "UPDATE clientes SET codigo = 5 WHERE codigo = 2. Ele vai tentar atualizar o código para 5 e vai atualizar esta chave também na tabela pedidos.

SET NULL (atribuir NULL) - Quando um registro na chave primária é atualizado, todos os campos dos registros referenciados a este são setados para NULL.

Exemplo: UPDATE clientes SET codigo = 9 WHERE codigo = 5; Na clientes o codigo vai para 5 e em pedidos, todos os campos cod_cliente com valor 5 serão setados para NULL.

SET DEFAULT (assumir o Default) - Quando um registro na chave primária é atualizado, todos os campos nos registros relacionados são setados para seu valor DEFAULT.

Exemplo: se o valor default do codigo de clientes é 999, então

UPDATE clientes SET codigo = 10 WHERE codigo = 2. Após esta consulta o campo código com valor 2 em clientes vai para 999 e também todos os campos cod_cliente em pedidos.

ON DELETE parametros:

NO ACTION (RESTRICT) - Quando um campo de chave primária está para ser deletado, a exclusão será abortada caso o valor de um registro na tabela referenciada seja mais velho. Este parâmetro é o default quando esta cláusula não recebe nenhum parâmetro.

Exemplo: ERRO em DELETE FROM clientes WHERE codigo = 2. Não funcionará caso o cod_cliente em pedidos contenha um valor mais antigo que codigo em clientes.

CASCADE - Quando um registro com a chave primária é excluído, todos os registros relacionados com aquela chave são excluídos.

SET NULL - Quando um registro com a chave primária é excluído, os respectivos campos na tabela relacionada são setados para NULL.

SET DEFAULT - Quando um registro com a chave primária é excluído, os campos respectivos da tabela relacionada são setados para seu valor DEFAULT.

Excluindo Tabelas Relacionadas

Para excluir tabelas relacionadas, antes devemos excluir a tabela com chave estrangeira.

Tudo isso está na documentação sobre CREATE TABLE: http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html

ALTER TABLE http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html

Chave Primária Composta (dois campos)

CREATE TABLE tabela (

codigo INTEGER,

data DATE,

nome VARCHAR(40),

PRIMARY KEY (codigo, data)

);