SQLite ROW Limit

Question

Possible Duplicate:
Limit an sqlite Table's Maximum Number of Rows

How can I limit the number of rows in my sqlite db? I would like to limit my db to 10 rows and give a message when an attempt to exceed that number is made. would this be done when the database is created or when querying the db

creation:

String sqlDataStore = "create table if not exists " 
+ TABLE_NAME_INFOTABLE + " ("+ BaseColumns._ID + " integer primary key autoincrement," 

         + COLUMN_NAME_SITE + "text not null," 
         + COLUMN_NAME_ADDRESS + "text not null,"
         + COLUMN_NAME_USERNAME + "text not null,"
         + COLUMN_NAME_PASSWORD + "text not null)";

      db.execSQL(sqlDataStore);

Query:

 Cursor cursor = database.query(databaseName.TABLE_NAME, null, null, null, null, null, databaseName.COLUMN_NAME, null);

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

This limit cannot be imposed with just a table definition.

Approaches include:

Using an INSERT trigger and RAISE (this requires an SQLite version with trigger support), or;
Put the INSERT access to the database behind a DAL/BLL that guards against more than N records being added. The UPDATE-instead-of-INSERT approach shown here is a variation.

edited May 23 '17 at 12:12

Community

1
1

answered Aug 27 '12 at 15:41

Triggers do exist for SQLite on Android – dymmeh Aug 27 '12 at 15:41
"This limit cannot be imposed with just a table definition." I think it can. You just have to use SQLite intrinsic functions (not standard SQL) to do it. See my answer [here](http://stackoverflow.com/a/12148135/562459). – Mike Sherrill 'Cat Recall' Aug 28 '12 at 10:40

Mike Sherrill 'Cat Recall' · Answer 2 · 2012-08-28T11:02:17.543

You can limit the number of rows using a CHECK constraint in the SQL DDL. Your application will have to trap the error that results from trying to add too many rows. There are other errors your app will have to trap, too. Disk full, use of NULL, etc.

The key point seems to be guaranteeing that the integer ID number is, in fact, an integer. SQLite's type affinities let you insert nonsense into an integer column.

sqlite> create table foo (n integer);
sqlite> insert into foo values ('wibble');
sqlite> select n from foo;
wibble

But the typeof() function can protect you from nonsense.

The typeof() function is a SQLite function. It's not standard SQL, so you'll have to rewrite the CHECK constraint below if you move to another dbms. (Not very likely since you're programming for android, but others who see this might be working in a different environment.)

create table test_limit (
  n integer primary key
    check (
      (typeof(n)='integer') and
      (n >=1 and n <= 10)
  )
);

Using typeof(n) allows inserting only integers. The range condition limits the number of integers you can insert. Briefly tested in version 3.7.9.

sqlite> insert into test_limit values (1.13);
Error: datatype mismatch
sqlite> insert into test_limit values (-2);
Error: constraint failed
sqlite> insert into test_limit values ('wibble');
Error: datatype mismatch
sqlite> insert into test_limit values (1);
sqlite> insert into test_limit values (2);
sqlite> insert into test_limit values (17);
Error: constraint failed

Later...

This seems be a simple, workable solution if your goal can be expressed as "limit the column 'id' to integers from 1 to 10 inclusive". I took some liberties with your code, so I could work directly in SQLite. You should look hard at the non-key columns. Since you have no natural key (no set of columns are unique except the id number), you don't really have much of a table. My SQL inserts below should make that problem clear, but it's a different problem than limiting the table to 10 rows.

create table test_limit (
  id integer primary key autoincrement,
  site text not null,
  address text not null,
  username text not null,
  password text not null,
  check (
    typeof(id) = 'integer' and
    (id >= 1 and id <= 10)
  )
);

-- 10 inserts.    
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');
insert into test_limit (site, address, username, password)
values ('site', 'address', 'username', 'password');

Attempting to repeat one of these inserts results in Error: constraint failed. Attempting to insert anything but an integer into the "id" column fails with Error: datatype mismatch.

SQLite ROW Limit

2 Answers2