Android SQLite - force increase on integer column

I have a table which store data on fuel used by a pa开发者_如何学Gorticular vehicle. One of the column is current_mileage. Is there any way I can constrain this table to only accept increasing values for this column? I.e. if row 1 has 1000 as the current_mileage, then row 2's current_mileage must be 1001 or above.

Many thanks, Barry

You could use a trigger to enforce this constraint.

Here's an example that assumes your table is named FuelConsumption, and contains a vehicle_id column (just guessing, you'll want to store more than one vehicle's fuel consumption? -- if not, just leave out the WHERE clause in the WHEN part):

CREATE TRIGGER trg_bi_FuelConsumption
BEFORE INSERT ON FuelConsumption
WHEN NEW.current_mileage <= (SELECT MAX(current_mileage) FROM FuelConsumption WHERE vehicle_id = NEW.vehicle_id)
BEGIN
    RAISE(ABORT, 'Fuel consumption cannot decrease');
END;

I would also advise putting a UNIQUE constraint on the combination of (vehicle_id, current_mileage). This will make sure that an index is created (thereby quite speeding up the WHEN portion of the trigger), and will catch identical values even before the trigger.

If you don't want to add a UNIQUE constraint, then at least add a (non-unique) index to your table, on those two columns.

Why not make current_mileage your primary key?

You cannot constrain that in the table creation. You will have to do it by your self programaticall, and, before inserting new data. So you can have something like:

Cursor c = db.rawQuery("SELECT current_mileage FROM table_name ORDER BY current_mileage DESC LIMIT 1");
c.moveToFirst();
int max = c.getInt(c.getColumnIndex("current_mileage"));

Then, you have to make sure that the next insert must contain a current_mileage that is greater than max.

Databases do not inherently know anything about order of the rows they store; it is not part of the theory around databases. This kind of logic is better served by business rules in your application.