Chiavi esterne in postgresql può essere violato da trigger

Ho creato alcuni tavoli, postgres, aggiunta di una chiave esterna di una tabella a un’altra, e impostare l’ELIMINAZIONE in CASCATA. Stranamente, ho alcuni campi che sembrano essere la violazione di questo limite.

È normale questo comportamento? E se è così, c’è un modo per ottenere il comportamento che mi vogliate, senza violazioni possibile)?

Edit:

Ho orginaly creata la chiave esterna come parte di CREAZIONE TABELLA, usando solo

... REFERENCES product (id) ON UPDATE CASCADE ON DELETE CASCADE

L’attuale codice pgAdmin3 dà è

ALTER TABLE cultivar
  ADD CONSTRAINT cultivar_id_fkey FOREIGN KEY (id)
      REFERENCES product (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;

Edit 2:

Per Chiarire, ho una forte sospetto che i vincoli sono controllati solo in caso di aggiornamenti/inserimenti accadere, ma sono poi mai guardato di nuovo. Purtroppo io non ne so abbastanza su postgres per scoprire se questo è vero o come campi, potrebbe finire nel database, senza che questi controlli in fase di esecuzione.

Se questo è il caso, c’è qualche modo per controllare tutte le chiavi esterne e risolvere questi problemi?

Edit 3:

La violazione di un vincolo può essere causato da un difetto di trigger, vedi sotto

  • solo per un controllo di integrità puoi postare la dichiarazione che hai utilizzato per creare il FK?
  • Che versione di PG sei?
  • Sto utilizzando la versione 8.3
  • Dare un piccolo esempio di come riprodurre il tuo problema. Ie sql per creare le tabelle, popolano etc.
  • Cosa è, non ho idea di come è finito in quella situazione. Tutto quello che so è che ho creato due tabelle, ha aggiunto il foreign key inserito un carico di dati e consentire al sistema di eseguire un po’. Allora, mentre stavo per correggere gli errori – mi sono imbattuto in questi campi, dove il foreign key constraint non regge. Ora ho pensato che non era possibile, ma forse sono solo implementato in un one-shot modo? Controllato con inserimenti/aggiornamenti ma poi mai guardato di nuovo?
  • Ho ricreato l’errore e ha postato un test per la mia risposta qui sotto.

 

2 Replies
  1. 24

    Ho provato a creare un semplice esempio che mostra un vincolo di chiave esterna in corso di esecuzione. Con questo esempio voglio dimostrare che io non sono autorizzati a immettere i dati che violano la fk e mi dimostrano che se il fk non è a posto durante l’inserimento, e abilitare il fk, fk vincolo genera un errore che mi dice dati violi il fk. Quindi non vedo come si dispone di dati nella tabella che viola un fk che è a posto. Sto 9.0, ma questo non dovrebbe essere diverso 8.3. Se si riesce a mostrare un esempio che dimostra che il problema che potrebbe aiutare.

    --CREATE TABLES--
    CREATE TABLE parent
    (
      parent_id integer NOT NULL,
      first_name character varying(50) NOT NULL,
      CONSTRAINT pk_parent PRIMARY KEY (parent_id)
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE parent OWNER TO postgres;
    
    CREATE TABLE child
    (
      child_id integer NOT NULL,
      parent_id integer NOT NULL,
      first_name character varying(50) NOT NULL,
      CONSTRAINT pk_child PRIMARY KEY (child_id),
      CONSTRAINT fk1_child FOREIGN KEY (parent_id)
          REFERENCES parent (parent_id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE child OWNER TO postgres;
    --CREATE TABLES--
    
    --INSERT TEST DATA--
    INSERT INTO parent(parent_id,first_name)
    SELECT 1,'Daddy'
    UNION 
    SELECT 2,'Mommy';
    
    INSERT INTO child(child_id,parent_id,first_name)
    SELECT 1,1,'Billy'
    UNION 
    SELECT 2,1,'Jenny'
    UNION 
    SELECT 3,1,'Kimmy'
    UNION 
    SELECT 4,2,'Billy'
    UNION 
    SELECT 5,2,'Jenny'
    UNION 
    SELECT 6,2,'Kimmy';
    --INSERT TEST DATA--
    
    --SHOW THE DATA WE HAVE--
    select parent.first_name,
           child.first_name
    from parent
    inner join child
            on child.parent_id = parent.parent_id
    order by parent.first_name, child.first_name asc;
    --SHOW THE DATA WE HAVE--
    
    --DELETE PARENT WHO HAS CHILDREN--
    BEGIN TRANSACTION;
    delete from parent
    where parent_id = 1;
    
    --Check to see if any children that were linked to Daddy are still there?
    --None there so the cascade delete worked.
    select parent.first_name,
           child.first_name
    from parent
    right outer join child
            on child.parent_id = parent.parent_id
    order by parent.first_name, child.first_name asc;
    ROLLBACK TRANSACTION;
    
    
    --TRY ALLOW NO REFERENTIAL DATA IN--
    BEGIN TRANSACTION;
    
    --Get rid of fk constraint so we can insert red headed step child
    ALTER TABLE child DROP CONSTRAINT fk1_child;
    
    INSERT INTO child(child_id,parent_id,first_name)
    SELECT 7,99999,'Red Headed Step Child';
    
    select parent.first_name,
           child.first_name
    from parent
    right outer join child
            on child.parent_id = parent.parent_id
    order by parent.first_name, child.first_name asc;
    
    --Will throw FK check violation because parent 99999 doesn't exist in parent table
    ALTER TABLE child
      ADD CONSTRAINT fk1_child FOREIGN KEY (parent_id)
          REFERENCES parent (parent_id) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE CASCADE;
    
    ROLLBACK TRANSACTION;
    --TRY ALLOW NO REFERENTIAL DATA IN--
    
    --DROP TABLE parent;
    --DROP TABLE child;
  2. 5

    Tutto quello che ho letto finora sembra suggerire che i vincoli sono solo controllato i dati inseriti. (O quando il vincolo è creato) Per esempio il manuale sul set di vincoli.

    Questo senso e se il database funziona correttamente dovrebbe essere abbastanza buono. io sono ancora curioso di sapere come sono riuscito ad aggirare questo o se ho appena letto la situazione sbagliato e non c’è mai stata una reale violazione del vincolo per iniziare con.

    in ogni modo, caso chiuso :-/

    ——- AGGIORNAMENTO ——–

    C’era sicuramente la violazione di un vincolo, causato da un difetto di trigger. Ecco uno script per replicare:

    -- Create master table
    CREATE TABLE product
    (
      id INT NOT NULL PRIMARY KEY
    );
    
    -- Create second table, referencing the first
    CREATE TABLE example
    (
      id int PRIMARY KEY REFERENCES product (id) ON DELETE CASCADE
    );
    
    -- Create a (broken) trigger function
    --CREATE LANGUAGE plpgsql;
    CREATE OR REPLACE FUNCTION delete_product()
      RETURNS trigger AS
    $BODY$
        BEGIN
          DELETE FROM product WHERE product.id = OLD.id;
          -- This is an error!
          RETURN null;
        END;
    $BODY$
      LANGUAGE plpgsql;
    
    -- Add it to the second table
    CREATE TRIGGER example_delete
      BEFORE DELETE
      ON example
      FOR EACH ROW
      EXECUTE PROCEDURE delete_product();
    
    -- Now lets add a row
    INSERT INTO product (id) VALUES (1);
    INSERT INTO example (id) VALUES (1);
    
    -- And now lets delete the row
    DELETE FROM example WHERE id = 1;
    
    /*
    Now if everything is working, this should return two columns:
    (pid,eid)=(1,1). However, it returns only the example id, so
    (pid,eid)=(0,1). This means the foreign key constraint on the
    example table is violated.
    */
    SELECT product.id AS pid, example.id AS eid FROM product FULL JOIN example ON product.id = example.id;
    • Non c’è mai stata una reale violazione del vincolo.
    • Ho capito che ho maneggiato un po ‘ questa situazione in modo strano, così ho fatto un piccolo sforzo in più e replicato la situazione che ha causato il mio problema iniziale.

Lascia un commento