Re: [OT] SQLite autoincrement vs primary keys / rowid
- Posted by ZNorQ Aug 19, 2016
- 1744 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
Hi Greg,
You solved it for me - the solution is the unique( fieldA, fieldB, .. ) clause. I was so sure I already tested that, but obviouly I didn't... Oh, and yes, I forgot to mention that it was the combination of several fields I wanted as primary key (or unique combination). UNIQUE solves this. Thanks for the valuable feedback.
I tested the follwing SQL, and worked like a charm;
( id integer primary key autoincrement,
name text not null,
cityId integer not null,
unique( name, cityId )
);
( 'Kenneth', 4317 ), /* first combination */
( 'Ronny', 4317 ),
( 'Kim', 4317 ),
( 'Kenneth', 4318 ),
( 'Kenneth', 4319 ),
( 'Kenneth', 4317 ); /* Already entered, FAILS as intended */
SELECT * from employee;
RESULT:
1|Kenneth|4317
2|Ronny|4317
3|Kim|4317
4|Kenneth|4318
5|Kenneth|4319
Error: near line 28: UNIQUE constraint failed: employee.name, employee.cityId
