0

how can i create a table with 2 primary key with $cordovaSQLite?

My code: 

$cordovaSQLite.execute(db, 'CREATE TABLE IF NOT EXISTS mytable(id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER PRIMARY KEY, text TEXT)');

It is possibile?

Luca Vall
  • 1
  • I'd suggest taking a look [at this question](http://stackoverflow.com/questions/6154730/sqlite-multi-primary-key-on-a-table-one-of-them-is-auto-increment). It's also advised that `AUTOINCREMENT` isn't used as it [imposes a lot of overhead](http://www.sqlite.org/autoinc.html). – Ankh Jan 23 '17 at 10:38

1 Answers1

0

Setting two primary keys is not possible, but you can set a unique constraint on any fields you want to act like a primary key. You can emulate autoincrementing on this field by looking at this question: SQLite auto-increment non-primary key field. Hope this helps.

Community
  • 1
  • 1
Ikenna Anthony Okafor
  • 628
  • 7
  • 25
  • Yes, i have modified my code with this: CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER, UNIQUE (id, num)). But, what is the correct query to insert a record if the id and num there aren't in table? – Luca Vall Jan 27 '17 at 09:01
  • If the id column is auto incremented, its value will be generated by the SQLite engine. Assuming it is not autoincremented (like the num column), the correct for both columns is: `INSERT INTO mytable (id, num, text) VALUES ((SELECT IFNULL(MAX(id), 0)) + 1 FROM mytable), (SELECT IFNULL(MAX(num), 0)) + 1 FROM mytable), 'text here')`. Refer [here](http://stackoverflow.com/questions/6982173/sqlite-auto-increment-non-primary-key-field) for additional information. – Ikenna Anthony Okafor Jan 28 '17 at 14:00
  • For just the `num` column (in this case, the id column is autoincremented), the query is: `INSERT INTO mytable (num, text) VALUES ((SELECT IFNULL(MAX(num), 0)) + 1 FROM mytable), 'text here')`. – Ikenna Anthony Okafor Jan 28 '17 at 14:02