Sqlite make sure input data is correct length

问题:

I asked a question to which I got a great answer but which bring many other questions.

Say I've created a table:

 CREATE TABLE test(my_id INTEGER(2));

How can I make sure that when INSERTING data in there (or imporing from csv atually) the field is exactly an INTEGER(2), not INTEGER(1) or anything else it would dynamically stretch to..? If I cannot are there no memory/performance issues with this?

Thanks!

回答1:

All values imported from CSV files are strings (but type affinity might change that).

To ensure that values are in a certain range, add an explicit constraint:

CREATE TABLE test(
    my_id INTEGER CHECK (my_id BETWEEN 0 AND 100)
);

Regardless of how they're declated, integer values are stored in 1, 2, 3, 4, 6, or 8 bytes depending only on the magnitude of the value.