On 18 Feb 2015, at 11:38am, Flakheart <gary.flakheart at gmail.com> wrote:

> I can deal with single table inserts but I think this would be some sort of
> nested insert statement?

There are no statements in SQL which can modify more than one table.  So you 
will need to use a number of commands, one for each table.

However you should enclose all the commands in a transaction:

BEGIN;
INSERT INTO tableA ...;
INSERT INTO tableB ...;
INSERT INTO tableC ...;
COMMIT;

That way if any one of the commands is not executed because of an error, none 
of the commands will be executed and you won't have half a recipe in your 
system.  From your schema it looks like you are going to have to INSERT into 
your recipe table first, then find out what number was assigned to recipe_id 
and use that when modifying the other tables.  It's possible to do all this 
within the same transaction.

It might be a good idea to specify in your schema that all _id fields are 
INTEGERs as follows:


recipe_instructions table    recipe_id  INTEGER           ; 
                              recipe_step  INTEGER         ; 
                              step_description TEXT     ; .

By the way, it would be possible to use FOREIGN KEYs to ensure that, for 
example, the 'recipe_id' column in your ingredients table only had genuine 
recipe_id numbers in it.  However, if you trust your software and your 
programming it's not necessary to do this.

Simon.

Reply via email to