SQLite CVSTrac

*** 31,43 ****
             CONSTRAINT fk_foo_id REFERENCES a(id) ON DELETE CASCADE
    );
  
! Table <code>bar</code> has a foreign key reference to the primary key column in the <code>foo</
  code> table. Although SQLite supports this syntax (as well as named foreign key constraints), it ignores 
  them. So if you want the references enforced, you need to create triggers to do the job.
  
  Triggers were added to SQLite version 2.5, so most users can take advantage of this feature. Each 
! constraint must have three triggers: one for <code>INSERT</code>s, one for <code>UPDATES</
! code>s, and one for <code>DELETES</code>s. The <code>INSERT</code> trigger looks like this:
  
    CREATE TRIGGER fki_bar_foo_id
    BEFORE INSERT ON bar
--- 31,43 ----
             CONSTRAINT fk_foo_id REFERENCES a(id) ON DELETE CASCADE
    );
  
! Table bar has a foreign key reference to the primary key column in the foo</
  code> table. Although SQLite supports this syntax (as well as named foreign key constraints), it ignores 
  them. So if you want the references enforced, you need to create triggers to do the job.
  
  Triggers were added to SQLite version 2.5, so most users can take advantage of this feature. Each 
! constraint must have three triggers: one for INSERTs, one for UPDATES</
! code>s, and one for DELETESs. The INSERT trigger looks like this:
  
    CREATE TRIGGER fki_bar_foo_id
    BEFORE INSERT ON bar
***************
*** 46,53 ****
        WHERE  (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
    END;
  
! If your foreign key column is not <code>NOT NULL</code>, the trigger's
! <code>WHERE</code> clause needs to an extra expression:
  
    CREATE TRIGGER fki_bar_foo_id
    BEFORE INSERT ON bar
--- 46,53 ----
        WHERE  (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
    END;
  
! If your foreign key column is not NOT NULL, the trigger's
! WHERE clause needs to an extra expression:
  
    CREATE TRIGGER fki_bar_foo_id
    BEFORE INSERT ON bar
***************
*** 57,64 ****
               AND (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
    END;
  
! The <code>UPDATE</code> statements are almost identical; if your foreign key column is 
! <code>NOT NULL</code>, then do this:
  
    CREATE TRIGGER fku_bar_foo_id
    BEFORE UPDATE ON bar
--- 57,64 ----
               AND (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
    END;
  
! The UPDATE statements are almost identical; if your foreign key column is 
! NOT NULL, then do this:
  
    CREATE TRIGGER fku_bar_foo_id
    BEFORE UPDATE ON bar
***************
*** 67,73 ****
        WHERE  (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
    END;
  
! And if <code>NULL</code>s are allowed, do this:
  
    CREATE TRIGGER fku_bar_foo_id
    BEFORE UPDATE ON bar
--- 67,73 ----
        WHERE  (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
    END;
  
! And if NULLs are allowed, do this:
  
    CREATE TRIGGER fku_bar_foo_id
    BEFORE UPDATE ON bar
***************
*** 77,85 ****
              AND (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
    END;
  
! The <code>DELETE</code> trigger is, of course, the reverse of the <code>INSERT</code> and 
! <code>UPDATE</code> triggers, in that it applies to the primary key table, rather than the foreign key 
! table. To whit, in our example, it watches for <code>DELETE</code>s on the <code>foo</code> 
  table:
  
    CREATE TRIGGER fkd_bar_foo_id
--- 77,85 ----
              AND (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
    END;
  
! The DELETE trigger is, of course, the reverse of the INSERT and 
! UPDATE triggers, in that it applies to the primary key table, rather than the foreign key 
! table. To whit, in our example, it watches for DELETEs on the foo 
  table:
  
    CREATE TRIGGER fkd_bar_foo_id
***************
*** 89,98 ****
      WHERE (SELECT foo_id FROM bar WHERE foo_id = OLD.id) IS NOT NULL;
    END;
  
! This trigger will prevent <code>DELETE</code>s on the <code>foo</code> table when there are 
! existing foreign key references in the <code>bar</code> table. This is generally the default behavior 
! in databases with referential integrity enforcement, sometimes specified explicitly as <code>ON 
! DELETE RESTRICT</code>. But sometimes you want the deletes in the primary key table to 
  <q>cascade</q> to the foreign key tables. Such is what our example declaration above specifies, and 
  this is the trigger to to the job:
  
--- 89,98 ----
      WHERE (SELECT foo_id FROM bar WHERE foo_id = OLD.id) IS NOT NULL;
    END;
  
! This trigger will prevent DELETEs on the foo table when there are 
! existing foreign key references in the bar table. This is generally the default behavior 
! in databases with referential integrity enforcement, sometimes specified explicitly as ON 
! DELETE RESTRICT. But sometimes you want the deletes in the primary key table to 
  <q>cascade</q> to the foreign key tables. Such is what our example declaration above specifies, and 
  this is the trigger to to the job:

sqlite - Foreign Key Triggers