SQLite foreign key?

Question

I have 2 tables: A and B. A has a foreign key to B and B has a foreign key to A. But I cannot make a foreign key from A to B, because A is created before B.

How can I solve it when SQLite doesn't support Alter Table?

This is my sample database:

Create Table A(
  A_ID    INTEGER PRIMARY KEY,
  B_ID    INTEGER,
  A_DESCRIPTION    TEXT,

  FOREIGN KEY (B_ID) REFERENCES B(B_ID)
)

Create Table B(
  B_ID    INTEGER PRIMARY KEY,
  A_ID    INTEGER,
  B_DESCRIPTION    TEXT,

  FOREIGN KEY (A_ID) REFERENCES A(A_ID)
)

Answer 1

You have a syntax error: there is a comma missing before FOREIGN KEY in table B.

Otherwise, your schema is correct. (SQLite will interpret the REFERENCES clauses only when needed, so there is no problem creating a reference to a table that does not yet exist.)

Please note that inserting records will be problematic, unless you insert records with NULL values first and update them later when the referenced record exists. It might be a better idea to create deferred foreign key constraints.