Add a unique index that includes both columns. That will prevent you from inserting a record that contains a duplicate category_id/post_id pair. add_index :categories_posts, [ :category_id, :post_id ], :unique => true, :name => ‘by_category_and_post’
I would use the second junction table: MOVIE_CATEGORY_JUNCTION Movie_ID Category_ID The primary key would be the combination of both columns. You would also have a foreign key from each column to the Movie and Category table. The junction table would look similar to this: create table movie_category_junction ( movie_id int, category_id int, CONSTRAINT movie_cat_pk PRIMARY … Read more
One other option is to look at the system table sqlite_sequence. Your sqlite database will have that table automatically if you created any table with autoincrement primary key. This table is for sqlite to keep track of the autoincrement field so that it won’t repeat the primary key even after you delete some rows or … Read more