MySQL table with only a varchar as a foreign key

views:

520

answers:

+1 Q:

MySQL table with only a varchar as a foreign key

Hi,

I have a table with a single unique VARCHAR(512) field. I want to have another table hold a foreign key reference to this first table. Both tables use InnoDB. If I add a VARCHAR(512) key to the second table and add a foreign key constraint on it will the 512 bytes long data be held twice?

If so, is there a way to hold only a reference to the index and not to the varchar itself?

In short, my question is, in InnoDB is there an efficient way to hold foreign keys to long VARCHAR fields?

Thank you very much,

Yaniv

+1 A:

Yes, if you have a VARCHAR(512) column on the referencing table, the data will exist twice.

I recommend that you make the referencing table's foreign key refer to an integer primary key for the first table, not the 512-byte data. This is kind of what normalization is all about.

chaos 2009-08-16 21:50:35

But isn't it kind of a waste to add an integer key when the index for the varchar exists anyways?What I really want is a way to reference only the contents of the index.

Yaniv 2009-08-16 21:53:51

It's not a waste if you need a 'handle' to the row and your only other option is a 512-character varchar column. This idea of a 'way to reference only the contents of the index' seems like nonsense to me; you can't refer to anything but the values the index indexes.

chaos 2009-08-16 21:56:41

Are all of the data really 512 bytes long? Storage should be limited to the size of the record on each row. If possible it would be better to go to an integer primary and foreign key.

Turnkey 2009-08-16 22:00:20

The maximum allowed size is 512 bytes, but most of the entries are around 100 bytes long. This is why I used the VARCHAR type, as it should only use about 100 bytes for these and not waste space.

Yaniv 2009-08-16 22:07:34

ansaurus

tags:

views:

answers:

MySQL table with only a varchar as a foreign key

related questions