Menu
Can I restrict the data length for a column value in sqlite3?
ex: varchar name(5), Insert statement accepts more than 5 characters.
553
The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface. The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper bound on: The number of columns in a table. The number of columns in an index.
SQLite database files have a maximum size of about 140 TB. On a phone, the size of the storage (a few GB) will limit your database file size, while the memory size will limit how much data you can retrieve from a query. Furthermore, Android cursors have a limit of 1 MB for the results.
SQLite does not have an explicit TRUNCATE TABLE command like other databases. Instead, it has added a TRUNCATE optimizer to the DELETE statement. To truncate a table in SQLite, you just need to execute a DELETE statement without a WHERE clause. The TRUNCATE optimizer handles the rest.
You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there. And it will keep all 500-million characters intact. Your content is never truncated. SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardless of the value of N.
SQLite treats varchar(5) as text (i.e. unlimited string) but you can add a CHECK constraint to the column:
create table pancakes (
name text check(name is null or length(name) <= 5)
)
This will give you a "constraint failed" error if your name is too long:
sqlite> create table pancakes (name text not null check(length(name) <= 5));
sqlite> insert into pancakes (name) values ('1234');
sqlite> insert into pancakes (name) values ('12345');
sqlite> insert into pancakes (name) values ('123456');
Error: constraint failed
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
