Search code examples
phpsqliteunique-constraintunique-index

SQLITE create table with UNIQUE column

I am trying to create a table with an UNIQUE column in a PHP script but I get an error.

exec("sqlite3 /location/file.db 'CREATE TABLE $tablename (id INTEGER PRIMARY KEY AUTOINCREMENT,record_time_long UNIQUE VARCHAR(255),update_time_long UNIQUE VARCHAR(255),name VARCHAR(255),macaddress VARCHAR(255),longitude VARCHAR(255),latitude VARCHAR(255),sourcedevice VARCHAR(255),human_address VARCHAR(250),smce_hash VARCHAR(250),sm_hash VARCHAR(250),islowenergy VARCHAR(250),rssi INT(4));'");

When the script runs I get

SQLite3::prepare(): Unable to prepare statement: 1, no such table: tablename in /location/script.php on line 41PHP message: PHP Fatal error:  Uncaught Error: Call to a member function execute() on bool in /location/script.php:43

in the error.log of the NGINX webserver. So the table is not created. It works fine if I dont add the UNIQUE constraint after the column name.

I have also tried modifying my existing table right after the creation but I don't even get an error and the UNIQUE constraint is not added.

exec("sqlite3 /location/file.db 'CREATE UNIQUE INDEX random_name ON $tablename(update_time_long);'");

Please help me, how can I make a column UNIQUE in my PHP script?

Solution

  • UNIQUE keyword should be added after the Column type, i.e varchar(255).

    So change

    update_time_long UNIQUE VARCHAR(255)

    to

    update_time_long VARCHAR(255) UNIQUE