CODEWITHSUNDEEP
sqlite
user3501779
user3501779

Reputation: 169

Inserting a row at a specific position?

So I created a recipe database, and just now I've noticed I forgot to add an ingredient to the second recipe. The order of ingredients is obviously important here, so even if I add it now to the end of the table, it will still be the last when I SELECT the second recipe, when it should be the second ingredient.

Is there any way I can insert it in a specific position, or am I doomed and will have to create an index column specifying the order of the ingredients?

NOTE: This is a junction table, so there's no primary key here, thus I can't insert it using a specific primary key value.

EDIT: Basically I have three tables: Recipe, Ingredient, and RecipeIngredient many-to-many junction table.

Here's the RecipeIngredient junction table structure:

RecipeId: FK
IngredientId: FK
Quantity: REAL
UOM: TEXT
Notes: TEXT

Upvotes: 2

Views: 455

Answers (2)

MPelletier
MPelletier

Reputation: 16687

The rules of the First normal form (1NF) are strict on this:

  • There's no top-to-bottom ordering to the rows.

Meaning there is no way, in a proper database schema, that a record can be "missing at a certain position".

You are indeed "doomed" and

will have to create an index column specifying the order of the ingredients

Upvotes: 1

Keshav_Nandhan
Keshav_Nandhan

Reputation: 152

can you show the table structure? It is difficult to insert a row in specific position without primary key.

Upvotes: 0

Related Questions