Can a database detect that data is out of sequence if each datapoint has a sequential ID
Weird question. If you don't set a sequential ID as autonumber/autoincrement but sequence must not break, you should code it yourself.a) Don't allow user-entry for the field and add a (max(field)+1) either by an insert trigger or Stored Proc. (BTW it's always a good idea to deny direct access to tables and all insert/update/etc is done by e.g. SPs).
b) If must allow users to add field's value, validate it (insert trigger or even better if it's an "insert SP" with validation logic -- just as I described above) and rollback insert if ID is not prevID+1.But what's the point? What is your goal with this 'sequence logic' you are about to implement?If "trade data" you mention must be in sequence but it's not always true you should look into the business logic to see1) why and how the sequence breaks2) what is the best behavior of app/db if it happens (from a business point of view! - e.g. renumber? throw error but still save data? save data with timestamp and maintain data integrity at the end of the day? etc)3) how to avoid this -if possible- to happen in the futureSo IMHO focus on business logic and customer requirements, figure out what fits best their needs then code that.