PHP SQLite3 Check For Constraint Violation

PHP SQLite3 Check For Constraint Violation

Sometimes we may need to use a constraint in our database design (e.g UNIQUE constraint) and want to be able to handle when such violations occur for logging purposes or others.

The PHP library for SQLite3 provides a way for us to know the last error that happened – its error code and message using SQLite3::lastErrorCode and SQLite3::lastErrorMsg

From SQLite documentation on the error code list here, you can see that SQLITE_CONSTRAINT error code is 19. So to check for a constraint violation, it will look something like this

// $this->db is the SQLite3 object / class instance
$last_error_code = $this->db->lastErrorCode();
$last_error_msg  = $this->db->lastErrorMsg();
if( $this->db->lastErrorCode() == self::SQLITE3_CONSTRAINT ) {	
    // Handle CONSTRAINT violations here
}

However, the SQLite3 library for PHP does not provide a way for us to get extended error code (or at least, i’m unable to find it from their documentation page here). Hence, to check for exactly which constraint was violated (e.g UNIQUE), you will have to check the SQLite3::lastErrorMsg instead.

$last_error_msg  = $this->db->lastErrorMsg();
if( strstr($last_error_msg, 'UNIQUE') ) {
   //UNIQUE CONSTRAINT VIOLATION ...
}