2

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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