Menu
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)
)
219
What is a Foreign Key in SQLite? A foreign key is a way to enforce referential integrity within your SQLite database. A foreign key means that values in one table must also appear in another table. The referenced table is called the parent table while the table with the foreign key is called the child table.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
