Sterex Sterex - 1 month ago 10
SQL Question

Using VARCHAR as PRIMARY KEY for an 'ORPHAN' table

I'm to create an orphan table (no relationships with any other table whatsoever) that contains 3 columns.

  • Col1 - String field - VARCHAR(32) - Contains unique data not more than 32 characters

  • Col2 - String field - TEXT - Contains larger non-unique data of characters

  • Col3 - Numeric (Bool) - INT(1) - 0/1 for Flagging

I'm thinking of using Col1 as my PRIMARY KEY. I have done some research and see people argue that using a meaningless INT column as a PRIMARY KEY to avoid Foreign Key/Storage issues is the way to go.

However, IMO, since this is an orphan table, it should not matter. Besides, I would require to place an INDEX on Col1 anyway.

As a side note, I'm not expecting more than ~1000 rows in this table.

Thoughts please.

Answer Source

If col1 is your real primary key, there is no reason not to use it. Especially if the table is that tiny.

You would need to maintain a unique index on that column anyway, so by adding an artificial primary key you just add more overhead fon insert and delete operations (as two indexes must be maintained).

Unless you are referencing that PK from really, really many other rows (and other tables) you should just go with what is the natural primary for your business rules.