iOS Swift Sqlite UNIQUE constraint failed

Question

This is in my first view controller, it creates a table called event and tries to insert a row.

I delete my app and install. then I run..

my log then says:

Failure: UNIQUE constraint failed: event.eid: INSERT INTO event (eid,passcode) VALUES (1,0);

There is nothing in the table it is empty. Why does it say my Unique Constraint (primary Key) is failing?

it also inserts the row, even though it throws an error?

many thanks for your help!

 ///////////////////////////
 //creating event table
    if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS event (eid INTEGER PRIMARY KEY, 
    eventid INTEGER NOT NULL DEFAULT 0, passcode TEXT NOT NULL DEFAULT '', 
    lastTicket INTEGER NOT NULL DEFAULT 0, online INTEGER NOT NULL DEFAULT 0, 
    onsale INTEGER NOT NULL DEFAULT 0, lastscanned INTEGER NOT NULL DEFAULT 0, 
    wasOffline INTEGER NOT NULL DEFAULT 0, eventOver TEXT NOT NULL DEFAULT '', 
    avatar INTEGER NOT NULL DEFAULT 0, devID TEXT NOT NULL DEFAULT '', 
    localScans INTEGER NOT NULL DEFAULT 0)", nil, nil, nil) != SQLITE_OK {


        let errmsg = String(cString: sqlite3_errmsg(db)!)
        log.error("error creating table: \(errmsg)")
    }

    var sqlString = "SELECT * FROM event WHERE eid=1"
    if(getCount(sqlString:sqlString)==0) {

        let sqlInserString = "INSERT INTO event (eid,passcode) VALUES (1,0);"
        log.verbose(runSQL(sqlString:sqlInserString))

    }///if eid!



func runSQL(sqlString:String) -> (String) {
  var sqlMessage = ""
  var sqlPointer: OpaquePointer? = nil

  if sqlite3_prepare_v2(db, sqlString, -1, &sqlPointer, nil) == SQLITE_OK {
      if sqlite3_step(sqlPointer) == SQLITE_DONE {
          sqlMessage = "Success: " + sqlString
      } else {
          let errmsg = String(cString: sqlite3_errmsg(db)!)
          sqlMessage = "Failure: \(errmsg): " + sqlString
      }
  } else {
      let errmsg = String(cString: sqlite3_errmsg(db)!)
      sqlMessage = "Not prepared: \(errmsg): " + sqlString
  }
  sqlite3_finalize(sqlPointer)

  return(sqlMessage)

}

eid needs to be unique. You can't run the query INSERT INTO event (eid,passcode) VALUES (1,0); more than once without the error. — rmaddy

MikeT MikeT · Accepted Answer · 2018-02-16T01:06:33

Column eid being defined as eid INTEGER PRIMARY KEY, is a special case. The column will be an alias of rowid which has an implied UNIQUE constraint, as it's intended to uniquely identify a row.

Typically you would not assign a value when inserting a row, rather you would let SQLite assign a value.

Your insert should be either :-

let sqlInserString = "INSERT INTO event (eid,passcode) VALUES (NULL,0);"

or :-

let sqlInserString = "INSERT INTO event (passcode) VALUES (0);"

eid will then be a UNIQUE identifier as an INTEGER, initially 1 and then likely 2, 3 etc (note there is no guarantee that it will be 1 greater or even greater (although it will be greater until the highest possible id has been used 9223372036854775807); as such you should only assume that the value will be a unique integer).

iOS Swift Sqlite UNIQUE constraint failed

1 Answers