sqlite: Should I use a text column as primary key, or convert it by a hash function?

Question

I need to use a text column as primary key for my sqlite database. I also have the possibility to use a hash function to get an 32bit/64bit int from my text values (I want to use as primary key). So I could use these int hash values instead my text value as primary keys. But then I need to store the raw text value additionally in the table.

But I read that sqlite is using a rowid internally, So I don't know if using a hash function will help anyway.

Will sqlite store the rowid or the text value (when I use it instead of the hash value) in their pages? When it stores the text value then I think it could blow off the pages and using a hash value would be better.

But I have a lack of knowledge here. I hope you can help me.

Answer 1

If the values in the text column are supposed to be unique, you're going to have to put a unique index on that column anyway.

The real question doesn't have to do with primary keys, it has to do with foreign key references. If a table has a column of unique integers and a column of unique text, you can use either one as the target for a foreign key reference. All foreign key references to the integer will require a join; some foreign key references to the text will not require a join. (Because the crucial information is carried in the key itself. Only those queries that need more columns besides the text key will need a join.)

Answer 2

Unfortunately yes, any indexes on strings (including usage as a primary key) take double space. Once for the value in the row, second time for the value in the index.

So if hashing meets your needs (you don't need sorting, lookup by ranges), using it will be a good idea.