CODEWITHSUNDEEP
androidsqlitesyntaxtriggersprimary-key
sean
sean

Reputation: 133

SQLIte ROW Limiting

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);

Upvotes: 0

Views: 392

Answers (2)

RightHandedMonkey
RightHandedMonkey

Reputation: 1728

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);

Upvotes: 1

sam.wang.0723
sam.wang.0723

Reputation: 321

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.

Upvotes: 0

Related Questions