Create Table with deferred foreign key referencing each other
<p>I'm new to sqlite and sql in gerneral so I don't know if my approach is reasonable. </p>

<p>I want to model inventory items that can be created, lent, returned and discarded. </p>

<p>I want to model this using two tables, one for items, containing an id, a name and a reference to the last transaction (created, lent, returned, ...) and a table of transactions containing an id transaction type, date, and a reference to the item.</p>

<p>Since creating only one table leaves the database in an inconsitent state with one table referencing a non existant table I thought of using a transaction to crate both tables at once, and defining the foreign keys as deferrable. Creation of a new item would have to be done together in one transaction with creating a "created" event to leave the database in a consistent state.</p>

<p>The following code gives me the error <code>Query Error: not an error Unable to execute multiple statements at a time</code> in sqliteman on linux.</p>

<pre><code>PRAGMA foreign_keys = ON;

begin transaction;
create table items (
id  integer primary key,
name char(30),
foreign key (last_transaction) references transactions(transaction_id) DEFERRABLE INITIALLY DEFERRED
);

create table transactions(
transaction_id integer primary key,
text char(100)
foreign key (item) references items(id) DEFERRABLE INITIALLY DEFERRED
);
commit transaction;
</code></pre>

<p>Does my approach make sense at all?
If yes, why does the code not work? (Did I make a mistake somewhere, or is what I'm trying impossible in mysql?)</p>

<p>Note: simply creating the tables in one transaction without the foreign key constraints gives the same error. (Could this be a similar Problem to: <a href="https://stackoverflow.com/questions/30760997/questions-about-create-table-and-transaction-in-sqlite3-of-python">this question</a>)</p>