Violation Foreig Key Contrait - Problemas com chave FK?

Firebird

11/01/2007

Pessoal agora que achei que estava pronta minha primeira S.Proc. :D
Me deparei com o problema para deletar :(

Isto ainda pq nem fiz o Update, espero que nao tenha porblemas tambem...hehehe

Bom,

Tenho uma tabela Meste e outra detalhe com um campo chamado sequencia que faz referencia, ai vou deletar em cascata, mais esta ocorrendo erro assim

[b:11dfb07f5a][color=red:11dfb07f5a]´violation foreign key contraint FK_Debito_1 on table Debito foreign key references are present for the record´[/color:11dfb07f5a][/b:11dfb07f5a]

Bom no delphi tenho um query para deletar assim
procedure TFConta.b5Click(Sender: TObject);
var
ok: boolean;
begin
if Tag = 1 then begin
beep;
ShowMessage(´Este comando não está diponivel para este evento!´);
Exit;
end;
if trim(edit1.text)=´´ then begin
showmessage(´Selecione o codigo que vc deseja excluir´);
end else
if application.messagebox(Pchar(´Deseja Excluir este Lançamento:´ + #13+´Mes do Lançamento: ´+dm.qtbcontalanDataExtenso.AsString + 13+´Dia/Data do Lançamento: ´+dm.qtbcontalanData.AsString +13+´Valor do Lançamento:  ´+dm.qtbcontalan.fieldbyname(´valor´).AsString+13+´Histórico:  ´+13+ dm.qtbcontalanhistorico.AsString), Pchar(´Excluir Dados´+Self.Caption), MB_ICONQUESTION
+ MB_YESNO) = IDYES then begin
try // começo do finally
  try //começo co except unie o laço
    with dm.qtbcontalan do
      begin
        close;
        sql.clear;
        sql.add(´delete from tbconta´); //codigo SQL para deletar linha
        sql.add(´where sequencia = ´+quotedstr(edit1.text)+´´);
        ExecSQL;
      end;
  except
      application.MessageBox(´Erro na Gravação! Tente Novamente!´,´Aviso´,mb_ok+mb_iconexclamation);
      ok:=false;
      editV; //procedure da unit funçoes

Antes quando nao usava S.Procedure funcionava, mais tambem estava um pouco diferente minha unit, mais agora nao funciona mais e mostra o erro acima:

Vou deixar postado minhas duas tabelas pra ver se tem haver
Tabela Mestre
SET SQL DIALECT 3;

SET NAMES ISO8859_1;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE TBCONTA (
    SEQUENCIA    INTEGER NOT NULL,
    CONTAD       VARCHAR(10),
    CONTAC       VARCHAR(10),
    VALOR        NUMERIC(15,2),
    DESCRICAO    BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    DATA         DATE,
    VALORD       NUMERIC(15,2),
    VALORC       NUMERIC(15,2),
    DC           CHAR(1),
    CONTA        VARCHAR(10),
    DATAEXTENSO  VARCHAR(30),
    MES          VARCHAR(2),
    ANO          VARCHAR(4),
    MARCAR       CHAR(1),
    HISTORICO    VARCHAR(100) COLLATE PT_PT,
    NOMECD       VARCHAR(50),
    NOMECC       VARCHAR(50),
    NUMCONTAD    VARCHAR(10),
    NUMCONTAC    VARCHAR(10),
    NOMECONTA    VARCHAR(50),
    NUMCONTA     VARCHAR(10)
);




/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE TBCONTA ADD CONSTRAINT PK_TBCONTA PRIMARY KEY (SEQUENCIA)
USING DESCENDING INDEX PK_TBCONTA;


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE DESCENDING INDEX IDXCONTA ON TBCONTA (SEQUENCIA);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: AUTOINCRCONTA */
CREATE TRIGGER AUTOINCRCONTA FOR TBCONTA
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.sequencia is null) then
  select coalesce(max(sequencia),0)+1 from tbconta into new.sequencia;
end
^


/* Trigger: RETRCONTA */
CREATE TRIGGER RETRCONTA FOR TBCONTA
ACTIVE AFTER DELETE POSITION 0
AS
begin
 UPDATE tbconta SET SEQUENCIA = SEQUENCIA - 1
 WHERE SEQUENCIA > OLD.SEQUENCIA;
end
^


SET TERM ; ^


E a Tabela Detalhe
SET SQL DIALECT 3;

SET NAMES ISO8859_1;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE DEBITO (
    SEQUENCIA  INTEGER NOT NULL,
    CONTAD     VARCHAR(15),
    VALOR      NUMERIC(15,2),
    HISTORICO  VARCHAR(100),
    MES        VARCHAR(2),
    ANO        VARCHAR(4),
    DATA       DATE,
    CONTA      VARCHAR(15)
);




/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE DEBITO ADD CONSTRAINT FK_DEBITO_1 FOREIGN KEY (SEQUENCIA) REFERENCES TBCONTA (SEQUENCIA) ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE DESCENDING INDEX IDXDEBITO ON DEBITO (SEQUENCIA);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: TR_AUTOINC_DEBITO */
CREATE TRIGGER TR_AUTOINC_DEBITO FOR DEBITO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text autoincremento */
  if (new.sequencia is null) then
  select coalesce(max(sequencia),0)+1 from debito into new.sequencia;
end
^


/* Trigger: TR_RETR_DEBITO */
CREATE TRIGGER TR_RETR_DEBITO FOR DEBITO
ACTIVE AFTER DELETE POSITION 0
AS
begin
  /* Trigger text retroceder debito*/
  UPDATE debito SET SEQUENCIA = SEQUENCIA - 1
  WHERE SEQUENCIA > OLD.SEQUENCIA;
end
^


SET TERM ; ^
Bom esta ai como criei as 2 tabelas

Agradeço a ajuda de todos
Adriano.


Adriano_servitec

Adriano_servitec

Curtidas 0

Respostas

Adriano_servitec

Adriano_servitec

11/01/2007

Ja sei o motivo, eh por causa desta trigger


/* Trigger: TR_RETR_DEBITO */
CREATE TRIGGER TR_RETR_DEBITO FOR DEBITO
ACTIVE AFTER DELETE POSITION 0
AS
begin
  /* Trigger text retroceder debito*/
  UPDATE debito SET SEQUENCIA = SEQUENCIA - 1
  WHERE SEQUENCIA > OLD.SEQUENCIA;
end
^


SET TERM ; ^


Mais no caso vou precisar tirar essa trigger aki tambem

/* Trigger: RETRCONTA */
CREATE TRIGGER RETRCONTA FOR TBCONTA
ACTIVE AFTER DELETE POSITION 0
AS
begin
 UPDATE tbconta SET SEQUENCIA = SEQUENCIA - 1
 WHERE SEQUENCIA > OLD.SEQUENCIA;
end
^


SET TERM ; ^


Pena pois eu usava ela para organizar o campo sequencia. :cry:
Gotaria de manter organizada a sequencia, mais ja que nao tem jeito, entao resolvi retirar as trigger


GOSTEI 0
POSTAR