SQLIte ROW Limiting

Question

I am trying to limit the number of rows in my SQLite Db to one. I have searched every where for a adequate solution but have not gotten any where. I have received many suggestions ( constraints, triggers, ...etc) but have not really found a solution that is easy to implement with my current skill level. I have decided to try triggers but cannot find where to properly write the code. I have the structure but do not know how to properly format the syntax. my code is below which of course is not working ( compiling errors). Any help instructing how to properly write this code for android would be greatly appreciated. I’ve already been all over the sqlite.org site ( http://www.sqlite.org/lang_createtrigger.html)

 public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub      

        String sqlDataStore = "create table if not exists " +
        TABLE_NAME_CREDENTIALS + " ("+ BaseColumns._ID + " integer primary key " + " ON CONFLICT FAIL ,"

                    + COLUMN_NAME_USERNAME + " TEXT NOT NULL " + " UNIQUE,"
                    + COLUMN_NAME_PASSWORD + " TEXT NOT NULL " + " UNIQUE);";

        db.execSQL(CREATE TRIGGER  " row_limiter " +  " BEFORE INSERT "
               + " ON "
               + " TABLE_NAME_CREDENTIALS " 
               + " FOR EACH ROW WHEN " 
               + ( SELECT  + "_id" + FROM + " TABLE_NAME_CREDENTIALS ") > "1"
               +    BEGIN
                    DELETE  FROM + "TABLE_NAME_CREDENTIALS"  +  WHERE  + "_id " > "1";
                     END



        db.execSQL(sqlDataStore);
    }

DB with primary key set to table:

  String sqlDataStore = "create table if not exists " +
                    TABLE_NAME_CREDENTIALS + " ("+ BaseColumns._ID + " NOT NULL,"

                                + COLUMN_NAME_USERNAME + " TEXT NOT NULL, "
                                + COLUMN_NAME_PASSWORD + " TEXT NOT NULL, "
                                + " PRIMARY KEY (username, passcode))";

insert:

contentValues.put(LoginDB.COLUMN_NAME_USERNAME, rUsername);
contentValues.put(LoginDB.COLUMN_NAME_PASSWORD, rPasscode);

possible duplicate: http://stackoverflow.com/questions/2035670/limit-an-sqlite-tables-maximum-number-of-rows — Redoman, Sep 11 '12 at 16:16
This is similar but not a duplicate. What you are referring to has to do with limits not triggers. From my knowledge Limit statements do not work in android SQLite.. — sean, Sep 11 '12 at 17:10

score 1 · Answer 1 · answered Sep 11 '12 at 17:20

Perhaps the simplest way to limit a table to one row would to be to use the primary key in your table and make sure you always set to a constant value when inserting or updating. No triggers or other tricks needed.

CREATE TABLE `limited_table` (
  `id` int(11) NOT NULL,
  `value` text ,
  PRIMARY KEY (`id`)
)

now when replacing, always use a constant in your SQL query

ContentValues args = new ContentValues();
args.put(KEY_ID, LIMITED_ID_CONST);
args.put(KEY_VALUE, value );
//Replace
db.replace(DATABASE_TABLE, null, args);

added primary for the table but am unsure of the syntax to set the primary key to the columns of row 1 — sean, Sep 12 '12 at 01:43

sam.wang.0723 · Answer 2 · 2012-09-11T16:24:42.720

0

Why not COUNT the row number and protect it before you insert? Why insist on SQL command? By the way, another workaround is to pre-create the limits of rows and use update, not insert.

edited Sep 11 '12 at 16:24

answered Sep 11 '12 at 16:19

sam.wang.0723

321
1
7

what you say sounds sensible but I am unable to visualize. Can you provide an example – sean Sep 11 '12 at 17:07
You can pre-insert 100 rows with dummy content, then only update those dummy rows, not insert new one. and protected with its COUNT <= 100 – sam.wang.0723 Sep 12 '12 at 04:01

SQLIte ROW Limiting

2 Answers2