Re: [OT] SQLite autoincrement vs primary keys / rowid
- Posted by ghaberek (admin) Aug 19, 2016
- 1719 views
You have to combine columns into a single primary key: http://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns
Edit 1: Scratch that. While this syntax technically works, it ignores the AUTOINCREMENT functionality on the id column. I'll do more research.
Edit 2: Do you just need to guarantee that a column is unique? Use the UNIQUE constraint instead: http://stackoverflow.com/questions/6154730/sqlite-multi-primary-key-on-a-table-one-of-them-is-auto-increment
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, UNIQUE(email));
sqlite> INSERT INTO users (name,email) VALUES('Greg Haberek','ghaberek@gmail.com');
sqlite> INSERT INTO users (name,email) VALUES('John Smith','john.smith@gmail.com');
sqlite> SELECT * FROM users;
id|name|email
1|Greg Haberek|ghaberek@gmail.com
2|John Smith|john.smith@gmail.com
sqlite> INSERT INTO users (name,email) VALUES('Greg Two','ghaberek@gmail.com');
SQL error: column email is not unique
-Greg
