(Resolvido) On delete cascade

[OSJunior]

Olá, pessoal.

Tenho uma tabela e queria saber como adiciono uma constraint FK on delete cascade.

Problemas:

- FK para uma PK de uma mesma tabela

- SQL Server 2005 (no 2008 consegui tranquilo)

Estou fazendo assim:

create table CATEGORIAS (
   ID_CATEGORIA   int                  identity,
   ID_PARENT         int                  null,
   NOME                 varchar(50)    not null,
   constraint PK_CATEGORIAS primary key (ID_CATEGORIA)
);
go

alter table CATEGORIAS
   add constraint FK_CATEGORI_REFERENCE_CATEGORI foreign key (ID_PARENT)
      references CATEGORIAS (ID_CATEGORIA)
         on delete cascade
go

[fulvio]

Boa tarde Orlando,

Estranho é que, de acordo com a documentação do 2008, quando possui referências circulares o cascade não é aceito. Abaixo segue a parte da documentação:

"As séries de ações referenciais em cascata disparadas por uma única DELETE ou UPDATE devem formar uma árvore que não contenha referências circulares. Nenhuma tabela pode aparecer mais de uma vez na lista com todas as ações referenciais em cascata que resultem de DELETE ou UPDATE. Da mesma forma, a árvore de ações referenciais em cascata não precisa ter mais que um caminho para nenhuma tabela especificada. Toda ramificação da árvore termina quando encontra a tabela para a qual NO ACTION foi especificada ou é padrão."

link: http://msdn.microsoft.com/pt-br/library/ms186973.aspx

[OSJunior]

No MSSql2008 consegui fazer sim.

Pois bem, acho que não vou mesmo conseguir fazer essa referência circular.

Para contornar isso, estou pensando em criar um trigger que delete o que eu preciso nas demais tabelas. Não estou conseguindo fazer com que isso ocorra. Criei, pois, assim o trigger:

CREATE TRIGGER TG_ApagaProduto ON TAB_CATEGORIA FOR DELETE
AS
    DECLARE @ID_CATEGORIA INT = (SELECT ID_CATEGORIA FROM deleted)    
    DELETE FROM TAB_PRODUTOS WHERE ID_CATEGORIA = @ID_CATEGORIA    
GO

[fulvio]

Mas no 1º post, o cascade estava referenciando a mesma tabela... na trigger que está criando, o delete está na tabela "TAB_PRODUTOS".

Quando rodou o script no 2008, não colocou a constraint na tabela "TAB_PRODUTOS" não?

Script da trigger:

CREATE TRIGGER TG_ApagaProduto ON CATEGORIAS FOR DELETE
AS
    DECLARE @ID_CATEGORIA INT 
    Set @ID_CATEGORIA = (SELECT ID_CATEGORIA FROM deleted)    
    DELETE FROM CATEGORIAS WHERE ID_CATEGORIA = @ID_CATEGORIA    
GO

[OSJunior]

Olá, fulvio.

Vou explicar melhor como estou estruturando.

Basicamente, são duas tabelas: categorias e produtos.

create table TAB_CATEGORIAS
(
    ID_CATEGORIA    INT IDENTITY,
    ID_PARENT        INT,
    NOME            VARCHAR(100)
    constraint PK_CATEGORIAS primary key (ID_CATEGORIA)    
);

alter table TAB_CATEGORIAS 
add constraint FK_CATEGORIA_REF_CATEGORIA 
foreign key (ID_PARENT)
references TAB_CATEGORIAS (ID_CATEGORIA);

create table TAB_PRODUTOS
(
    ID_PRODUTO        INT IDENTITY,
    ID_CATEGORIA    INT NOT NULL,
    NOME            VARCHAR(100),
    constraint PK_PRODUTOS primary key (ID_PRODUTO)
);

alter table TAB_PRODUTOS 
add constraint FK_PRODUTOS_REF_CATEGORIA 
foreign key (ID_CATEGORIA)
references TAB_CATEGORIAS (ID_CATEGORIA);

Ideia inicial: colocar FK_CATEGORIA_REF_CATEGORIA como on delete cascade para que toda vez que uma categoria for apagada, todos os produtos relacionados a ela também sejam excluídos.

Com trigger: simular o efeito do on delete cascade. Ou seja, toda vez que algo em TAB_CATEGORIAS for apagado, a trigger é disparada com a finalidade de apagar os itens correspondentes na tabela TAB_PRODUTOS.

O erro: The DELETE statement conflicted with the REFERENCE constraint "FK_PRODUTOS_REF_CATEGORIA". The conflict occurred in database "TESTES", table "dbo.TAB_PRODUTOS", column 'ID_CATEGORIA'.

Teoricamente é isto, certo?

O problema: criei a trigger, testei e não apagou nada. Ocorreu o erro de tentativa inválidade de exclusão. Ou seja, parece que a trigger não foi disparada.

Abraços

Editado Setembro 21, 2010 por OSJunior

[fulvio]

Dá para fazer um delete cascade da tabela TAB_CATEGORIAS para a tabela TAB_PRODUTOS.

O problema seria se uma PK da TAB_CATEGORIAS referenciasse uma coluna dela mesma. Dá para fazer a referência, mas não pode ser cascade....

Fiz um script abaixo. Se quiser utilizar as tabelas como temporárias, basta colocar uma # antes do nome da tabela, quando for criá-la. Dê uma olhada se é isto:

create table TAB_CATEGORIAS
(
    ID_CATEGORIA    INT IDENTITY,
    ID_PARENT        INT,
    NOME            VARCHAR(100)
    constraint PK_CATEGORIAS primary key (ID_CATEGORIA)    
);

create table TAB_PRODUTOS
(
    ID_PRODUTO        INT IDENTITY,
    ID_CATEGORIA    INT NOT NULL,
    NOME            VARCHAR(100),
    constraint PK_PRODUTOS primary key (ID_PRODUTO)
);


alter table TAB_PRODUTOS 
add constraint FK_CATEGORIA
foreign key (ID_CATEGORIA) references TAB_CATEGORIAS (ID_CATEGORIA) on delete cascade

insert into TAB_CATEGORIAS (id_parent, nome) values (1, 'Teste1')
insert into TAB_CATEGORIAS (id_parent, nome) values (2, 'Teste2')
insert into TAB_CATEGORIAS (id_parent, nome) values (3, 'Teste3')

insert into TAB_PRODUTOS (id_categoria, nome) values (1, 'Categoria1')
insert into TAB_PRODUTOS (id_categoria, nome) values (1, 'Categoria2')
insert into TAB_PRODUTOS (id_categoria, nome) values (1, 'Categoria3')
insert into TAB_PRODUTOS (id_categoria, nome) values (2, 'Categoria4')

select * from TAB_CATEGORIAS
select * from TAB_PRODUTOS

delete from TAB_CATEGORIAS where id_categoria=1

select * from TAB_CATEGORIAS
select * from TAB_PRODUTOS

[OSJunior]

Olá.

Na verdade, a tabela TAB_PRODUTOS já possuia a constraint desse jeito.

Acabei por deixar desse último jeito que colocou.

A trigger também não resolveu.

O único problema que deu foi quando necessitei remover a categoria pai, pois não removeu os produtos. Como a regra de negócio diz que só é permitido incluir em subcategorias, podemos dizer que, para o meu problema, houve solução.

Obrigado, meu caro.

[fulvio]

Por nada... rs. Precisando estamos ai!!! :.)