If I have a table with
How can I insert a new row between row n and row n+1? I don't think the ID or key field of these rows will be in order or this is impossible to do unless rewrite the whole data of this table to a new table.
Your database client does not order rows. Rows are entered wherever it sees fit and the order they are entered in the tables is entirely of no consequence.
Suppose I have an article that has 10 paragraphs. Let's say each paragraph is a row. Later, if I want to insert a new paragraph between the fifth and sixth paragraphs. How do I do it?
Thanks in advance.
Well you have to develop a numbering system that will accommodate this. Back in to good old days of coding Basic on my Commodore64 (yes that's a whopping 64K of RAM) every line of code had to be numbered (so you could create messy spagetti code with GOTO commands; eg, GOTO 40). A general convention at the time was to increment the line numbers (as you were writing the code) by a factor of 10; eg, 10, 20, 30, 40, ..., so that there was some scope to insert additional lines of code.
Another convention you may want to adopt that would be easier is to make your paragraph number field a float that way you could have 1,2,3,4,5 etc, but later on insert a 2.1, 2.2 etc and still be able to maintain order.
Just a couple of thoughts. Otherwise you have to develop some code so that when you want to insert a new paragraph between some others you update all the paragraph numbers to keep everything in sequence. Eg, You want to insert between 5 and 6. So the new record will become 6 and 6 ->7, 7-> 8, etc
SET paraNum = paraNum + 1
WHERE paraNum >= 6
Now you will have a "slot" where you can insert the new paragraph 6
INSERT INTO tableName
SET paraNum = 6, etc, etc