current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

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.

share|improve this question
up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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