6

I develop an application with Laravel 5 and use sqlite during development. I want to switch to something faster later on. I'd like to have a string as unique identifier for the users-table. Is this a problem in any way? For example with foreign keys? Or is there no difference to auto-increment integers?

norbertVC
  • 425
  • 1
  • 4
  • 12
  • 2
    String can be a primary key. Add "Unique" constraint. But you can't add "auto_increment" constraint . http://www.sqlite.org/lang_createtable.html#constraints – user2267379 Apr 16 '15 at 21:35

2 Answers2

8

The short answer : It's perfectly fine to use a string as a primary key.

The long answer : We are terrible at choosing strings to be primary keys.

What is a good primary key candidate ?

  1. It should be unique.
  2. It should rarely, if at all, change.

Now, You are probably thinking that your string would never change, and that it's extremely unique, Until it will stop being unique.

Another (minor) concern, is performance. Searching, Joining etc. is a bit faster on integers than on strings, mostly due to the length(numbers are normaly shorter than strings, so comparing is easier).

I would think long and hard on what string to use on the primary key, Most of the times it's a bad idea

Patrick
  • 3,289
  • 2
  • 18
  • 31
  • Thanks for your answer. The main reason why I want to use a string as identifier is that I use an thrid-party api service to get the actual user-data and want to user their id to be my id. Or is this a bad idea? – norbertVC Apr 16 '15 at 21:36
  • If you will save queries by using the third party id, and the id uniqueness is already a concern that they solve for you i don't see why it would be a bad idea – FabioCosta Apr 16 '15 at 21:38
  • I agree with Fabio, In essence it's their problem, you're just a consumer of their data. I do wonder what would happen if a primary key changes on their end though :) – Patrick Apr 16 '15 at 21:39
1

The indexing of string is a lot different than a incremental int but you can have it as a primary key without problems.

Collision detection when inserting is an issue that you'll have to handle yourself and wouldn't even exist on an auto increment situaiton.

From the foreign key stand point there wouldn't be any problem also, the only problem that you would face is that a char/varchar would be less efficient than an integer.

FabioCosta
  • 3,069
  • 6
  • 28
  • 50