PostgreSQL: Foreign key on string column

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.

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

• varchars use more space than ints

• you are more likely to have to update a varchar value than an int value, causing cascading updates

• 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.

StackExchange Database Administrators Q#89949, answer score: 3