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.

Answer 2

I believe your issue is actually how you are defining your foreign keys. Defining circular foreign keys works just fine for me. Try this query instead:

pragma foreign_keys=on; 

Create Table A(
  A_ID    INTEGER PRIMARY KEY,
  B_ID    INTEGER REFERENCES B(B_ID),
  A_DESCRIPTION    TEXT
);


Create Table B(
  B_ID    INTEGER PRIMARY KEY,
  A_ID    INTEGER REFERENCES A(A_ID),
  B_DESCRIPTION    TEXT
);

Answer 3

Sqlite does support ALTER TABLE, but in a limited capacity.

From the documentation (http://www.sqlite.org/lang_altertable.html) :

The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

Since you aren't using constraints to your foreign keys, you could create both tables first and then add the foreign keys later with an ALTER TABLE ADD query, which is one of the few alter table commands that comes with SQLite.

CREATE TABLE A(
  ID    INTEGER PRIMARY KEY,
  A_DESCRIPTION    TEXT
)

CREATE TABLE B(
  ID    INTEGER PRIMARY KEY,
  B_DESCRIPTION    TEXT
)

ALTER TABLE A ADD FOREIGN KEY B_ID B(ID)
ALTER TABLE B ADD FOREIGN KEY A_ID A(ID)

If you need to add constraints to your table, this question over here covers it

Answer 4

I believe I done it before by creating tables in a transaction.