[sqlite] Complex insert query to normalised database

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)

[sqlite] Complex insert query to normalised database

(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

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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,

[sqlite] Complex insert query to normalised database

> 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

[sqlite] Complex insert query to normalised database

"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:

[sqlite] Complex insert query to normalised database

"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

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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.

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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

[sqlite] Complex insert query to normalised database

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