Apologies in advance folks, please forgive the question and the formatting.
I have around 650,000 recipes and through lots of study have more or less
determined that the table structures below will give me a reasonable
normalised database.
That is not to say that it is perfect, but it is a start
AFAIK, you can't do 'nested insert', or, insert to multiple tables in one
call. Not from a single command line, or, from a view. You're pretty much
stuck with updating one table at a time.
It would be nice, however, problems can come up with a many-to-many
situation where the engine isn't sure wh
Hi,
I'm not an sqlite expert but in general I think you can insert in the
tables one by one, but in the right order. And make sure the different
inserts per recipe belong to one transaction (if one fails, the previous
are rolled back automatically).
You should probably also study https://www.s
Thanks Stephen. At least I know now so I can go ahead and create my inserts
from data from the form. I just have to be careful to make sure they are
done in an efficient order with the data in the right place.
The category one I have to be careful of as it isn't in a list control but a
string box,
On 18 Feb 2015, at 11:38am, Flakheart 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
I suspect that this is wrong as nobody has suggested it but isn't this what
triggers are meant to solve?
Staffan
On Wed, Feb 18, 2015 at 2:13 PM, Simon Slavin wrote:
>
> On 18 Feb 2015, at 11:38am, Flakheart wrote:
>
> > I can deal with single table inserts but I think this would be some sort
Thanks Gunnar. Having never used foreign keys before, I am up for a lot of
reading. Hope this isn't beyond me:):)
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80620.html
Sent from the SQLite mailing list archive at Nabb
Here is something I don't understand. From my reading of foreign keys, it
points to the unique id of the table record that it references right?
If I insert a recipe with a specific category and then a different recipe
that uses the same category, how then does this foreign key work without
storin
You should make the relation the other way around.
Remove the foreign key from category and add to recipe_data a field
recipe_category_id and add also to the recipe_data table a foreign key
FOREIGN KEY(recipe_category_id) REFERENCES category(category_id)
Then you have defined a 1 to many relat
(And if you go for the one to many in between table, then you shouldn't
add the recipe_category_id field to recipe-data and also not the foreign
key).
An alternative is when you say that one recipe_data can belong to at
most, say, 5 categories.
Then you can do without the extra table and add re
> On 19 Feb 2015, at 8:26am, Flakheart wrote:
>
> If I insert a recipe with a specific category and then a different recipe
> that uses the same category, how then does this foreign key work without
> storing duplicate categories in the category table?
>
> Then later on, I need a recipe to be a
Sorry, I accidentally included 'INTEGER' before 'REFERENCES'. Should have been
CREATE TABLE category(
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category_name TEXT
);
CREATE TABLE recipes_in_categories(
r_i_c_idINTEGER PRIMARY KEY AUTOINCREMENT,
rec
"FOREIGN KEY(recipe_category_id) REFERENCES category(category_id)"
Thank you gunnar. I don't understand it yet but will work hard at it. Once I
make up some dummy data to play with, it might get me a better idea of how
all this works.
One thing I have no lack of is recipes!
My ambition is to one
"I'm going to try to guess the conventions and style you're using but please
excuse me if I get it wrong."
I would not dare to criticise what I do not understand. I am incredibly
grateful!
Lots of study to do. Not that I sleep much any more:):)
--
View this message in context:
http://sqlite.1
On Wed, 18 Feb 2015 14:16:32 +0100
Staffan Tylen wrote:
> I suspect that this is wrong as nobody has suggested it but isn't
> this what triggers are meant to solve?
Triggers were invented before SQL defined what we now call Declarative
Referential Integrity (DRI). It is (I'm going to say) alway
15 matches
Mail list logo