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 relationship (each recipe_data belongs to at most one category and each category can belong to many recipe_data's). If you want a many to many relationship (which you want from what I read) then you need one extra table in between with two fields recipe_data_category recipe_id category_id FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id) FOREIGN KEY(category_id) REFERENCES category(category_id) something like that. Gunnar On 02/19/2015 09:26 AM, Flakheart wrote: > 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 > storing duplicate categories in the category table? > > Then later on, I need a recipe to be a member of multiple categories. I have > no idea how this would work. > > I'm at sea here. > > CREATE TABLE recipe_data( > recipe_id INTEGER PRIMARY KEY AUTOINCREMENT, > recipe_name TEXT, > recipe_version TEXT, > recipe_lastupdate TEXT, > recipe_favourite INTEGER, > recipe_deleted INTEGER, > recipe_description TEXT, > recipe_notes TEXT, > recipe_servings INTEGER, > recipe_peparationtime TEXT, > recipe_cookingtime TEXT, > recipe_totaltime TEXT > ); > > CREATE TABLE category( > category_id INTEGER PRIMARY KEY AUTOINCREMENT, > category_name TEXT, > recipe_id INTEGER, > FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id) > ); > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80623.html > Sent from the SQLite mailing list archive at Nabble.com. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
