1. [OT] SQLite autoincrement vs primary keys / rowid

I know this might not be the place to ask this, however over the years I've seen a lot of bright people here with a wide range of knowledge - so I give it a go (I've tried to find solutions on google, but I've failed there - even on stackoverflow).

This is related to the SQLite application.

The objective here is to have an autoincrement function for uniquely identifying each record, but I don't want it to be a primary key. Further, I want to ensure that each id is unique, so if even the last record have been deleted I will get the next unused id (which autoincrement makes sure of).

ROWID
I've tried using the rowid, they can't be used as foreign keys at all - hence the following statement ends in an error;

  • create table employees( personID integer references person ( rowid ) );

AUTOINCREMENT and the PRIMARY KEY
You can only create fields with autonumbering capacity (afaik) with one of the following statements;

  • someID integer primary key /* Doesn't ensure duplicates - if last record is deleted, you'll get same id on next insert*/
  • someID integer primary key autoincrement /* Ensures unique numbers, but still becomes primary key... */

However, both takes away the ability to make other fields as a primary key.

Further, one could create a table keeping track of the last used id for each table, and then uses triggers to update both tables. However, that doesn't guarantee unique id's - unless you're clever (I'm not) and can find ways around it... Further, it complicates things a lot, and creates more elements one have to keep track of..

PRIMARY KEY on other fields
Since you can't have 2 or more primary keys in SQLite (or most other databases), using the autoincrement removes this possibility.

IN ESSENCE, WHAT I WANT..

  • Having guaranteed unique row ids that can be referenced as foreign keys
  • In addition using other field(s) as primary key

Does anyone know if it's me who's totally misunderstood the concept, or if there are good solutions to my predicament?

PS! I'm no guru at all in SQL, so über-technical jargons related to the subject will probably not be understood by me... :)

Kenneth aka ZNorQ

new topic     » topic index » view message » categorize

2. Re: [OT] SQLite autoincrement vs primary keys / rowid

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

new topic     » goto parent     » topic index » view message » categorize

3. Re: [OT] SQLite autoincrement vs primary keys / rowid

ghaberek said...

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;

DROP TABLE if exists employee;

CREATE TABLE employee
( id integer primary key autoincrement,
name text not null,
cityId integer not null,
unique( name, cityId )
);

INSERT INTO employee ( name, cityId ) values
( '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

new topic     » goto parent     » topic index » view message » categorize

Search



Quick Links

User menu

Not signed in.

Misc Menu