Skip to content
Advertisement

Data inserting in between the rows in table

I have a table which has around 1,000,000 records. I noticed that when I insert a record it does not get inserted at last instead it gets inserted in between

Example

My table (testing)

Si. Name. Address.
1. Test. 1/2 test
2. Test. 3/4 test

Si has a unique index created but no primary keys defined

So when I make an insert into table

Insert into testing (si,name,address) Values (3., Test., 5/6 test)

I get the below

Si. Name. Address.
1. Test. 1/2 test
3. Test. 5/6 test
2. Test. 3/4 test

How do I fix this issue.

What I know is that the value will normally get inserted at the last but this alone does this way.

Answer

Your “problem” isn’t a problem at all.

This is a relational database we’re talking about. Imagine it as a basket full of apples. When you put a new apple into it, how will you know which one of them was inserted before this one? You can’t.

The same goes with inserts into a relational database table – row can be inserted anywhere.

If you want to return rows in certain order, you must use the ORDER BY clause. And, of course, have something to sort data by. In your example, that’s the si column.

Therefore, instead of

select * from testing;

you’d

select * from testing order by si;

That’s how it works; there’s nothing you can do about it. Sometimes it’ll look as if you can omit order by, but that’s misleading and you can’t rely on it. Always use ORDER BY.