mardi 27 janvier 2015

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.





Aucun commentaire:

Enregistrer un commentaire