Dúvida Chave estrangeira e deleção em cascada

[Jefferson Monteiro]

Pessoal, boa noite! Estou precisando de uma ajuda em um script que criei para um trabalho da faculdade. Sou iniciante e estou com algumas dúvidas.

Bom estou postando o script do início do banco. A tabela usuário tem relacionamento de 1 para 1 com a tabela pessoa. A tabela funcionario herda da tabela pesFisica que por sua vez herda da tabela pessoa.

Quando insiro um registro na tabela usuario pego o id_usuario (PK) gerado e utilizo na inserção de registro na tabela pessoa para o campo id_usuario (FK). Antes eu utilizava o tipo Serial para todos os meus ids, tanto chave primária como chave estrangeira, mas li que o correto é o campo serial apenas para chave primária e o tipo Integer para chave estrangeira. Está correto?

Estou tendo um grande problema na exclusão de registros. Declarei na chave estrangeira da tabela pessoa "ON Delete Cascade" que deveria me garantir que ao deletar um registro da tabela pai o registro associado na tabela filho fosse também deletado, mas não é isso que acontece. Quando deleto um registro da tabela usuario não existe a deleção do registro correspondente nas tabelas pessoa, pesFisica e funcionario. E quando tento excluir um registro da tabela pessoa não sou barrado informando erro de restrição de chave estrangeira, ele simplesmente deleta.

Alguém poderia ajudar?

CREATE TABLE usuario (

id_usuario SERIAL NOT NULL,

email VARCHAR(20) NOT NULL CONSTRAINT unq_email UNIQUE,

senha VARCHAR(10) NOT NULL,

tp_usuario VARCHAR(10) NOT NULL,

CONSTRAINT pk_id_usuario PRIMARY KEY (id_usuario)

);

CREATE TABLE pessoa (

id_pessoa SERIAL NOT NULL,

id_usuario INTEGER NOT NULL,--(FK)

nome VARCHAR(45) NOT NULL,

tel_movel VARCHAR(10),

tel_fixo VARCHAR(10),

tel_comercial VARCHAR(10),

sexo VARCHAR(10) NOT NULL,

CONSTRAINT pk_id_pessoa PRIMARY KEY (id_pessoa),

CONSTRAINT fk_id_pessoa_id_usuario FOREIGN KEY (id_usuario) REFERENCES usuario (id_usuario) ON DELETE CASCADE

);

CREATE TABLE pesFisica (

id_pesFisica SERIAL NOT NULL,

cpf VARCHAR(11) NOT NULL CONSTRAINT unq_cpf UNIQUE,

dt_nascimento DATE NOT NULL,

CONSTRAINT pk_id_pesFisica PRIMARY KEY (id_pesFisica)

)INHERITS (pessoa);

CREATE TABLE funcionario (

id_funcionario SERIAL NOT NULL,

endereco VARCHAR(45) NOT NULL,

numero VARCHAR(5) NOT NULL,

bairro VARCHAR(20) NOT NULL,

cep VARCHAR(8) NOT NULL,

cargo VARCHAR(10) NOT NULL,

CONSTRAINT pk_id_funcionario PRIMARY KEY (id_funcionario)

)INHERITS (pesFisica);

[Angélica B. Bortolini]

Jefferson ,

Pelo que eu observei, tu não precisaria ter todas estas tabelas, apenas uma (PESSOA), e para indicar se a "pessoa" é física ou jurídica teria um campo de flag, e para indicar se é funcionário, teria outro flag.

Não sei se estou correta, mas a impressão que tive é que tu estava tentando montar as tuas tabelas como faria classes em Orientação a Objeto, se for o caso, não faça isso, modelagem de banco é diferente.

Não sei se consegui explicar direito, mas se continuares com dúvida pergunte, que conforme for possível, continuo respondendo.

[Kakao]

Quando deleto um registro da tabela usuario não existe a deleção do registro correspondente nas tabelas pessoa, pesFisica e funcionario

Eu criei as suas tabelas e para mim funcionou a deleção em cascata:

insert into usuario (email, senha, tp_usuario)
values (
'fulano@x.com', 'senha', 'tipo'
);

select * from usuario;
 id_usuario |    email     | senha | tp_usuario 
------------+--------------+-------+------------
          1 | fulano@x.com | senha | tipo
(1 row)

insert into pessoa (id_usuario, nome, tel_movel, tel_fixo, tel_comercial, sexo)
values (
1, 'fulano', '848484', '383940', '84859', 'masculino'
);

select * from pessoa;
 id_pessoa | id_usuario |  nome  | tel_movel | tel_fixo | tel_comercial |   sexo    
-----------+------------+--------+-----------+----------+---------------+-----------
         1 |          1 | fulano | 848484    | 383940   | 84859         | masculino
(1 row)

delete from usuario where id_usuario = 1;

select * from pessoa;
 id_pessoa | id_usuario | nome | tel_movel | tel_fixo | tel_comercial | sexo 
-----------+------------+------+-----------+----------+---------------+------
(0 rows)

Mas veja que as chaves (única, primária e estrangeira) não são herdadas. Assim as linhas correspondentes das tabelas filho (pesFisica e funcionario) não são deletadas. Para que isso aconteça você deve repetir as declarações de chave em cada tabela filho.

E quando tento excluir um registro da tabela pessoa não sou barrado informando erro de restrição de chave estrangeira, ele simplesmente deleta.

A restrição é só para a tabela referenciada e assim é possível deletar as linhas das tabelas referenciadoras.

Declarei na chave estrangeira da tabela pessoa "ON Delete Cascade" que deveria me garantir que ao deletar um registro da tabela pai o registro associado na tabela filho fosse também deletado

A chave estrangeira não cria relação pai/filho. O termo correto é tabela referenciada/referenciadora.

A observação da Angélica com relação à orientação a objeto é pertinente. O mecanismo de herança do postgresql não é o mesmo conceito de herança de OO.

Você fez bem em postar o que fez porque demonstra o seu esforço mas o seu modelo está confuso. Tente de novo e depois poste que agente tenta ajudar.

Editado Maio 18, 2012 por Kakao

[Jefferson Monteiro]

Angélica e Kakao muito obrigado pela ajuda! Realmente estou montando meu banco baseado no meu modelo do OO. Um outro profissional da área havia me orientado também a não fazer, mas segundo meu orientador da faculdade, tem que ser como o modelo em OO. Ele pediu para que o banco refletisse o diagrama de Classes.

Estou um pouco desorientado..

Eu criei as suas tabelas e para mim funcionou a deleção em cascata:

Kakao, fiz o teste como você: adicionei o usuário e depois adicionei registros na tabela pessoa referenciando o usuario. Fiz a deleção e a mesma realmente ocorreu em cascata.

Fiz outro teste adicionei um usuário e depois adicionei uma pessoa física (pesFisica) - como pesFisica herda os atributos de pessoa quando insiro dados em pesFisica, pessoa também é povoada -. Aí realizei a deleção deste novo usuário. O usuario foi deletado, mas os registros nas tabelas pessoa e pesFisica permaneceram íntegros.

É bastante complicado pois fui orientado a tratar o BD da mesma forma que o OO. Para resolver este problema só vejo uma solução (seguindo o modelo que criei) tratar isto no java, fazendo com que o Hibernate vá em todas as tabelas e delete/consulte/atualize o registro (linha) que possua o id_usuario desejado. Também pensei ao invés de herdar, criar um relacionamento 1 para 1 das tabelas, mas irá mudar o modelo...

Vocês me orientam outra solução?

Desde já obrigado!

Abs.