Strings as Primary Keys in SQL Database

Strings as Primary Keys in SQL Database

Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you're making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn't necessarily use a string for a table that has millions of rows, but the amount of performance slowdown you'll get by using a string on smaller tables will be minuscule to the headaches that you can have by having an integer that doesn't mean anything in relation to the data.

Databases - String as Primary key?

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

Should I use strings as primary keys for a Web site that's big on SEO?

The URL makes a bad primary key for a few reasons - one being that every time you insert a new record, your pages will fragment because it has to physically reorganize the entire table and stick the new record where it belongs alphabetically. Index the URL column and you'll get all the lookup performance you need. Rebuilding an index every time you insert is preferable to what is in some ways rebuilding the table.

Another (as Josh pointed out) is that URLs are highly similar, so comparisons will be extremely slow (compared to an int that is) - for two items - 2007 and 2008 inside website.com/Type/Car/Country/Usa/Manufacturer/Ford/Year/ you have to cover a lot of ground - 56 characters (plus the three for 200) before you hit a unique.

Use string as primary key to store words

I would be surprised if there were enough words that you needed to partition the table. Of course if your "words" are really genetic sequences or something, I might be off there.

In any case, one of the primary purposes of a primary key is to support foreign key relationships. So, if there is any possibility that another table might refer to this table, then you want to take that into account.

Integer foreign keys are generally preferable, because they are a fixed length -- and that is a little more efficient for indexes. In addition, four-byte integers are probably smaller than the average word length, so they save on storage of the foreign key as well.

That would be balanced against an additional 4 bytes in the words table itself. On balance, I usually add synthetic primary keys.

How to use String property as primary key in Entity Framework

This is the proper way of creating a PK without Identity Autoincrement enabled:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string FooId { get; set; }

Two string columns as primary key

There is no problem with using two strings as keys in your database. There is no need to go through all that trouble just to get integers keys, because it technically could be faster. This is basically also what Kevin says in the selected answer on the question you link.

SQLITE: Appending string to primary key

In SQLite, || can be used for concatenation.

So Try:

UPDATE
    PLAYERS
SET
    NAME = NAME || 'OLD';

SQL Language Expressions

The || operator is "concatenate" - it joins together the two strings
of its operands.

---

Related Topics