Implement/use foreign keys in SQLite?

Question

How can I implement a foreign key in SQLite? I was thinking something like this:

CREATE TABLE job (_id INTEGER PRIMARY KEY AUTOINCREMENT, employer_id INTEGER, ...);
CREATE TABLE employer(_id INTEGER, employer_name TEXT NOT NULL, ...);

Where employer_id is the _id from the table employer. Would this work? Is there another fast, maybe less prone to error way? Maybe with triggers?

Answer 1

See SQLite (3.6.19) Foreign Key Support

(Earlier version of SQLite do not support enforced FK relationships.)

Answer 2

Maybe I don't understand the question, but if it's the constraint you want, just do this:

ALTER TABLE Job
  ADD FOREIGN KEY (employer_id)
    REFERENCES Employer(_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;