none
deletar dados relacionados na tabela RRS feed

  • Pergunta

  • Amigos desenvolvedores, depois de muito apanhar lhes peço ajuda.

    Possuo uma tabela A e a ela estão relacionadas as tabelas B.C.D.E e F caso eu queira excluir um dado da tabela A não consigo pois alguns dados de outras tabelas estão relacionados a ela. a tabela A é minha tabela pai e as demais são as tabelas filho. Preciso que, quando eu deletar um dado da tabela pai, os dados das tabelas filhos também sejam deletados.

    Ex: na tabela B.C.D e E recebe dado que é Fk, ou seja, vem da tabela pai.

    Como fazer para eu excluir um pai e ao mesmo tempo excluir os filhos nas tabelas caso existam.

    Alguém poderia por favor ajudar ?
    quarta-feira, 24 de outubro de 2012 11:31

Respostas

  • Já entendi o problema, não sei se vou conseguir explicar... Mas vou tentar.

    O erro que está dando na criação da tabela HARDWARE é devido a FK referenciada à tabela COLABORADOR.

    Então, vamos analisar somente as tabelas COLABORADOR, HARDWARE E LOCALIDADE. Esqueçamos as outras.

    A FK da tabela COLABORADOR que se referencia à tabela LOCALIDADE (EC_cd_localidade) "diz" o que? Que se uma localidade for excluída, os registros da tabela COLABORADOR que tiverem ligação também serão excluídos.

    Adicionalmente, existe uma FK na tabela HARDWARE referenciando a tabela COLABORADOR (EH_cd_registro_colaborador). O que "diz" essa FK? Que se um colaborador for excluído, automaticamente serão excluídos os registros da tabela HARDWARE que tiverem ligação.

    Com isso, se um usuário excluir uma localidade, o SQL removerá os colaboradores, devido a FK EC_cd_localidade e removerá os hardwares, devido a FK EH_cd_registro_colaborador.

    Aí vem a pergunta: se o usuário fizer isso (remover uma localidade), pra que servirá a FK EH_cd_localidade, da tabela HARDWARE?

    Pra nada! Pois, como vimos acima, o hardware já terá sido removido pela cascata LOCALIDADE -> COLABORADOR -> HARDWARE. Então a cascata LOCALIDADE -> HARDWARE (gerada pela FK EH_cd_localidade) falharia (ou pelo menos não teria sentido).

    Então é isso que o SQL está te falando: "Cara, você está criando uma cascata estranha aí."

    Então para resolver, ou você remove (na tabela HARDWARE) a FK EH_cd_registro_colaborador ou a EH_cd_localidade.

    Parece-me que o mais indicado seria remover a FK que referencia a tabela LOCALIDADE (EH_cd_localidade), justamente porque se uma localidade for excluída o hardware também já será excluído pela cascata com o colaborador.

    Por outro lado, se você remover a FK EH_cd_registro_colaborador, se o usuário excluir um colaborador, o hardware a ele ligado não será removido.

    Pra finalizar, eu diria até que a existência do campo cd_localidade é questionável, pois se for verdadeira a premissa de que todo hardware terá um colaborador atrelado, então através da tabela COLABORADOR sempre existirá a opção de se chegar à localidade do hardware.

    Bem, quis detalhar e explicar tudo isso pra não lhe dar simplesmente a resposta e você não saber o motivo do problema.

    Espero que tenha conseguido.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 25 de outubro de 2012 13:49
    Moderador

Todas as Respostas

  • Jonathan, de uma olhada no delete em cascata, acredito que é o que procura :

    http://gustavomaiaaguiar.wordpress.com/2011/01/22/excluses-em-cascata-e-auto-referncia-no-sql-server/


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 24 de outubro de 2012 11:46
  • na verdade seria para deletar os dados da tabela pai e da fila e da neta através da tabela pai
    quarta-feira, 24 de outubro de 2012 13:46
  • Então...

    Como o Alexandre mencionou, o que você precisa é do Delete Cascade nas Foreign Keys. Precisará recriar as Foreign Keys com a opção on Delete Cascade.

    O exemplo abaixo não usa Delete Cascade. Assim sendo, ao tentar excluir um registro que possua filhos (netos, etc), será apresentado o erro que você mencionou.

    Create Table TabelaPai   (CodigoPai   int Primary Key)
    Create Table TabelaFilho (CodigoFilho int Primary Key, CodigoPai int Foreign Key References TabelaPai(CodigoPai))
    Create Table TabelaNeto  (CodigoNeto  int Primary Key, CodigoFilho int Foreign Key References TabelaFilho(CodigoFilho))
    
    Insert Into TabelaPai   Values (1), (2)
    Insert Into TabelaFilho Values (10,1), (20,1), (30,2)
    Insert Into TabelaNeto  Values (100,10), (200,10), (300,20), (400,20), (500,30)
    
    Delete TabelaPai Where CodigoPai = 1
    

    Msg 547, Level 16, State 0, Line 1
    The DELETE statement conflicted with the REFERENCE constraint "FK__TabelaFil__Codig__060FB360". The conflict occurred in database "Plastigel", table "dbo.TabelaFilho", column 'CodigoPai'.
    The statement has been terminated.

    Para resolver (de acordo com o que você quer), as Foreign Keys precisam estar configuradas para deleção em cascata, conforme segue:

    -- Apaga as tabelas-teste para recriá-las
    Drop Table TabelaNeto
    Drop Table TabelaFilho
    Drop Table TabelaPai
    
    ---------------------
    
    Create Table TabelaPai   (CodigoPai   int Primary Key)
    Create Table TabelaFilho (CodigoFilho int Primary Key, CodigoPai int Foreign Key References TabelaPai(CodigoPai) on Delete Cascade)
    Create Table TabelaNeto  (CodigoNeto  int Primary Key, CodigoFilho int Foreign Key References TabelaFilho(CodigoFilho) on Delete Cascade)
    
    Insert Into TabelaPai   Values (1), (2)
    Insert Into TabelaFilho Values (10,1), (20,1), (30,2)
    Insert Into TabelaNeto  Values (100,10), (200,10), (300,20), (400,20), (500,30)
    
    Delete TabelaPai Where CodigoPai = 1
    
    Select * From TabelaPai
    Select * From TabelaFilho
    Select * From TabelaNeto


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 24 de outubro de 2012 15:46
    Moderador
  • eu possuo uma tabela que possui 4 referencias (FK) o sql não permite criar as 4 referencias com on delete cascade
    quarta-feira, 24 de outubro de 2012 18:11
  • Permite sim.

    Veja que no exemplo abaixo possuo uma tabela com 4 FKs. Todas com cascata.

    Create Table TabelaPai  (CodigoPai  int Primary Key)
    Create Table TabelaPai2 (CodigoPai2 int Primary Key)
    Create Table TabelaPai3 (CodigoPai3 int Primary Key)
    Create Table TabelaPai4 (CodigoPai4 int Primary Key)
    
    Create Table TabelaFilho
    (
     CodigoFilho int Primary Key,
     CodigoPai   int Foreign Key References TabelaPai(CodigoPai) on Delete Cascade,
     CodigoPai2 int Foreign Key References TabelaPai2(CodigoPai2) on Delete Cascade,
     CodigoPai3 int Foreign Key References TabelaPai3(CodigoPai3) on Delete Cascade,
     CodigoPai4 int Foreign Key References TabelaPai4(CodigoPai4) on Delete Cascade
    )


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 24 de outubro de 2012 18:58
    Moderador
  • e se tiver mais um filho relacionado a tabela pai ?

    A mensagem de erro é essa: 

    Mensagem 1785, Nível 16, Estado 0, Linha 1
    Introducing FOREIGN KEY constraint 'EH_cd_localidade' on table 'HARDWARE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Mensagem 1750, Nível 16, Estado 0, Linha 1
    Could not create constraint. See previous errors.
    quarta-feira, 24 de outubro de 2012 19:20
  • Mas isso só deveria acontecer se a chave primária da tabela pai fosse uma chave primária composta. É este o caso?

    Post aqui o script de criação da tabela-pai por favor.

    E post também o script que está dando erro, das criações das FKs.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 25 de outubro de 2012 10:08
    Moderador
  • Tabela de LOCALIDADE

    CREATE TABLE LOCALIDADE
    (
    	cd_localidade int primary key,
    	nm_localidade varchar(30) NULL,
    	nm_endereco_localidade varchar(60) NULL,
    	nm_bairro_localidade varchar(20) NULL,
    	nr_cep_localidade char(9) NULL,
    	nm_cidade_localidade varchar(15) NULL,
    	nr_telefone_localidade char(14) NULL,
    	ds_uf char(2),
    	nm_pais varchar(10),
    	cd_usuario int NULL,
    	dt_cadastro datetime,
    
    	constraint E_cd_usuario foreign key (cd_usuario) references USUARIO (cd_usuario)
    )

    Tebela COLABORADOR

    CREATE TABLE COLABORADOR
    (
    	cd_registro_colaborador bigint primary key,
    	nm_colaborador varchar(50) null,
    	nr_ramal varchar(8) null,
    	nm_email varchar(100) null,
    	nm_setor_colaborador varchar(50) null,
    	ds_cargo_gestao int null,
    	nm_cargo_gestao varchar(50) null default 'não possui',
    	cd_localidade int,
    	cd_usuario int,
    	dt_cadastro datetime,
    
    	constraint EC_cd_usuario foreign key (cd_usuario) references USUARIO (cd_usuario),
    	constraint EC_cd_localidade foreign key (cd_localidade) references LOCALIDADE (cd_localidade) on Delete Cascade
    )

    Tabela FORNECEDOR

    CREATE TABLE FORNECEDOR
    (
    	cd_fornecedor int primary key,
    	nm_fornecedor varchar(50) NULL,
    	nm_endereco varchar(60) NULL,
    	nm_bairro varchar(20) NULL,
    	nr_cep char(9) NULL,
    	nm_cidade varchar(15) NULL,
    	nr_telefone char(14) NULL,
    	nm_contato_reponsavel varchar(50) NULL,
    	nr_celular_contato_responsavel varchar(16) NULL,
    	nm_site_fornecedor varchar(50) NULL,
    	nm_email_fornecedor varchar(50) NULL,
    	cd_usuario int NULL,
    	
    	constraint EF_cd_usuario foreign key (cd_usuario) references USUARIO
    )

    Tabela Hardware

    CREATE TABLE HARDWARE
    (
    	cd_hardware int primary key NOT NULL,
    	nm_hardware varchar(50) NULL,
    	nm_modelo varchar(40) NULL,
    	nm_marca varchar(40) NULL,
    	cd_serie varchar(35) NULL,
    	cd_fornecedor int NULL,
    	cd_localidade int NULL,
    	cd_registro_colaborador bigint NULL,
    	nm_ambiente varchar(12) NULL,
    	cd_usuario int NULL,
    
    	constraint EH_cd_fornecedor foreign key (cd_fornecedor) references FORNECEDOR (cd_fornecedor) on Delete Cascade,
    	constraint EH_cd_registro_colaborador foreign key (cd_registro_colaborador) references COLABORADOR (cd_registro_colaborador) on Delete Cascade,
    	constraint EH_cd_localidade foreign key (cd_localidade) references LOCALIDADE (cd_localidade) on Delete Cascade,
    	constraint EH_cd_usuario foreign key (cd_usuario) references USUARIO (cd_usuario)
    )

    a tabela de hardware ja da erro:

    Mensagem 1785, Nível 16, Estado 0, Linha 1
    Introducing FOREIGN KEY constraint 'EH_cd_localidade' on table 'HARDWARE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Mensagem 1750, Nível 16, Estado 0, Linha 1
    Could not create constraint. See previous errors.

    As outras nem tentei criar mediante o erro, tem como eu contornar essa situação ?

    Agradeço pela ajuda !

    quinta-feira, 25 de outubro de 2012 12:17
  • Coloca por favor a criação da tabela USUARIO.

    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 25 de outubro de 2012 12:40
    Moderador
  • Me desculpe, realmente esqueci dela.

    Segue abaixo.

    create table USUARIO
    (
    	cd_usuario int primary key,
    	nm_login varchar(50),
    	ds_senha varchar(25),
    	dt_cadastro datetime,
    	ds_status int,
    )

    quinta-feira, 25 de outubro de 2012 12:41
  • Esse erro está acontecendo pois o SQL está entendendo que há uma referência cíclica. Pode ser um "engano" de interpretação por parte do SQL ou pode realmente haver uma referência cíclica.

    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 25 de outubro de 2012 12:49
    Moderador
  • como posso contornar essa situação ?

    Há uma maneira ?

    quinta-feira, 25 de outubro de 2012 12:50
  • Já entendi o problema, não sei se vou conseguir explicar... Mas vou tentar.

    O erro que está dando na criação da tabela HARDWARE é devido a FK referenciada à tabela COLABORADOR.

    Então, vamos analisar somente as tabelas COLABORADOR, HARDWARE E LOCALIDADE. Esqueçamos as outras.

    A FK da tabela COLABORADOR que se referencia à tabela LOCALIDADE (EC_cd_localidade) "diz" o que? Que se uma localidade for excluída, os registros da tabela COLABORADOR que tiverem ligação também serão excluídos.

    Adicionalmente, existe uma FK na tabela HARDWARE referenciando a tabela COLABORADOR (EH_cd_registro_colaborador). O que "diz" essa FK? Que se um colaborador for excluído, automaticamente serão excluídos os registros da tabela HARDWARE que tiverem ligação.

    Com isso, se um usuário excluir uma localidade, o SQL removerá os colaboradores, devido a FK EC_cd_localidade e removerá os hardwares, devido a FK EH_cd_registro_colaborador.

    Aí vem a pergunta: se o usuário fizer isso (remover uma localidade), pra que servirá a FK EH_cd_localidade, da tabela HARDWARE?

    Pra nada! Pois, como vimos acima, o hardware já terá sido removido pela cascata LOCALIDADE -> COLABORADOR -> HARDWARE. Então a cascata LOCALIDADE -> HARDWARE (gerada pela FK EH_cd_localidade) falharia (ou pelo menos não teria sentido).

    Então é isso que o SQL está te falando: "Cara, você está criando uma cascata estranha aí."

    Então para resolver, ou você remove (na tabela HARDWARE) a FK EH_cd_registro_colaborador ou a EH_cd_localidade.

    Parece-me que o mais indicado seria remover a FK que referencia a tabela LOCALIDADE (EH_cd_localidade), justamente porque se uma localidade for excluída o hardware também já será excluído pela cascata com o colaborador.

    Por outro lado, se você remover a FK EH_cd_registro_colaborador, se o usuário excluir um colaborador, o hardware a ele ligado não será removido.

    Pra finalizar, eu diria até que a existência do campo cd_localidade é questionável, pois se for verdadeira a premissa de que todo hardware terá um colaborador atrelado, então através da tabela COLABORADOR sempre existirá a opção de se chegar à localidade do hardware.

    Bem, quis detalhar e explicar tudo isso pra não lhe dar simplesmente a resposta e você não saber o motivo do problema.

    Espero que tenha conseguido.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 25 de outubro de 2012 13:49
    Moderador
  • Conseguiu entender, Jonathan?

    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    sexta-feira, 26 de outubro de 2012 22:19
    Moderador