3

I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it.

So for example:

create table "Item" 
(
    "Name" VARCHAR(254) PRIMARY KEY
);

CREATE TABLE "Uses" --new table
(
    id SERIAL PRIMARY KEY NOT NULL,
    "itemName" VARCHAR(254) NOT NULL,
    FOREIGN KEY ("itemName") REFERENCES "Item" ("Name")
);

I was wondering, if each row of Uses will have a reference(memory address) to the Item tables column - that way not copying the value around.

Or will it indeed copy that value, causing superfluous space loss.

Also would like a general opinion on using a VARCHAR as a primary and foreign key. Should maybe better alter the old database to give it a proper id?

Thank you very much.

3 Answers 3

3

varchar keys are okay, but they do have some issues:

  1. varchars use more space than ints
  2. you are more likely to have to update a varchar value than an int value, causing cascading updates
  3. might not be appropriate in internationalized applications (i.e. different values for different languages)

To answer your particular question, no there won't be a pointer to the value, the actual value will be stored.

I rarely use varchars in key columns. Sometimes I'll use short char columns like ISO country codes, for convenience.

Also, you can just use the text type in PostgreSQL, as varchar uses text internally. Also, I'd recommend using unquoted snake_case column names.

0

If the value of your varchar is significantly large and in the same time it repeats extensively in the table "uses", your performance would be inferior to the approach using a generated key of type int, long or even a GUID.

Following apply:

  • Since uses replicates the primary key of the Item, more disk space will be necessary if this key is large.
  • An index is build both on the uses and in the item, replicating the value of the large column.
  • If using another key, the index of item name is still required, to enforce uniqueness.
  • Each query will perform more IO to retrieve data. As of 2017, this is a bigger problem than the actual disk usage.
0

I think the more important question is whether the primary key is at risk of changing.

Although it’s possible to have a change in primary cascade to the foreign keys, it’s preferable not change the primary key in the fist place, as cascading the changed primary key places an extra burden on the DBMS, and introduces an element of uncertainty.

The main reason you would want to allow the primary key to change is if you’re re-engineering the database.

Broadly, there are two main types of primary key. A surrogate primary key is purely arbitrary, while a natural primary key is based on a meaningful value. The surrogate primary key is generally preferable since there’s no need to change it afterwards.

Coming back to your question, using a string primary/foreign key is acceptable. The real potential weakness in your design is the possibility that that the Name value will keep changing making it a moving target for the foreign key.

A typical surrogate primary key is an auto-incremented integer, partly out of laziness, and partly out of the convenience of having a sequence number which represents the original insertion order.

If you can be sure that your string will always be unique and is unlikely to change (and doesn’t take up too much space), then it’s acceptable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.