Is it okay to use string as your ID in a MySQL table?

Question

I have designed my database and I use varchar to make unique name. I made a table products and each product has a product code, and this code is unique, it cannot be repeated and mostly this is my primary key in parent table. The idea is, for example I have a candle, and candles have different colors so a different product, I am able to make product name distinct by its product code. I didn't use or name a table as an indexable id which is of type int and that it auto_increments when new item is inserted. I just used the unique code as an indexer to my other tables on foreign keys.

I do not know if this is of great idea, I am very new to PHP MySQL and I got some problem when it comes to inserting values into different tables so I use the product code to link them both. Now I just knew about mysqli_insert_id stuff which could have solve my problem a long time ago.

So can I just use a non-standard row id of string than row id of type int???

Answer 1

Personally I would say to use an auto inc ID. Using your product code as an ID / primary key means you can't (or will be harder to) change the product code later on down the line which may cause problems.

To elaborate:

If you end up expanding your database so that your products are linked to various tables, using a product code as your ID means that if you want to change your product code you will have to update each entry in ALL tables. Using an ID that isn't the product code means you just have to update the code in one place (stock table) and the ID's will do the rest as you join the stock table to get the product code.

Answer 2

Big disadvantage of string ids is that it make indexes (much) bigger, which could affect performance.

I would keep it simple and go with standard auto increment int.

I also agree with @hd1 and @webnoob answers -- row id should be constant and independent from value which it describes -- classic wrong (in my opinion) row id is American SSN or any other unique number assigned by government to man or company.

Answer 3

Any unique [and ideally non-changing] value, group of values can be used as a Key/Primary Key in a database. The main reason that surrogate keys [eg: autoincremented INT columns] are so common in mySQL is because:

1. For a long time foreign keys were a no-go, [InnoDB has become usable only recently] and keeping string keys synchronized across several tables without them is a nightmare.
2. An index on a string column has a length, and can be shorter than the column itself. This is so you can keep your indexes compact, but can also cause issue issues. eg: A key on a name column with length of 5 collides on names Tom Cruise and Tom Clancy because the key is Tom C.

Answer 4

The pros and cons of natural vs surrogate keys will continue to be argued about for as long as we have relational databases. I say: do what you like, but if you are going to use a surrogate, try to make sure that, where possible, there is also a way of uniquely identifying rows by means of a natural key.