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.
