Autoincrement Primary Key For Constants We are having an issue that is probably due to some poor design choice or lack of understanding of how to use primary keys.

We have a simple constants table

CREATE TABLE IF NOT EXISTS `constants` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) NOT NULL,
  `subcategory` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)


We reference the unique rows using the id column.

E.g. if(id == 30) do something

Now, our issue arises when we sync between dev and production. On dev, we are constantly trying our new things/testing features. We are adding and deleting constants. So our ID column doesn't map into our production database. So, if we tried the same code again on production:

e.g. if(id == 30) do something

It points to a different record.

My initial thought is that we just make the category/subcategory columns the primary key but I have read that you should almost always use a GUID for a primary key.
Primary keys is a key concept of the relational model. From a pure practical way, in general you have 2 options:


Choose as a primary key one of the candidate keys: that is, a column or combination of columns that unequivocally identifies the row. For example, in Twitter, we could think that the user name or the user email in a users tables is a candidate key, so both could be used for the primary key
Create an arbitrary identifier that is created on insertion time (MySQL's AUTO_INCREMENT is a paradigmatic example, which we could generate on the same users table)


There are some reasons to choose one over the other (some good and some bad): For example, setting meaningful columns as primary key makes later harder to change those values, as many other tables or logic could depend on it. That could be a reason for or against it. For example, most sites like Twitter actually use an internal id so that you can easily change your username and email. Some ORMs require, or make the logic easier if you all your tables have an auto_increment int or bigint called id. Numeric ids are shorter, which in some specific cases may make the foreign keys or other parts shorter, and maybe more efficient (that is the case for MySQL's InnoDB in many cases due to its internal structure). On the other side, having and arbitrary id as a primary key may not be adequate for your logic, as there is not direct connection between it and the actual row.

Having said that, in my opinion -and that is without knowing your internals- you should give a human-recognizable identifier to your constants and query though that column:

if (name == "kill_all_humans"))
  do something


Whether name should be a primary key or a secondary key (and having a SERIAL in addition) is probably going to be of lesser importance, assuming your table has just a few values (not millions). That way, you can set and read the value independently of it having an arbitrary identifier. That way your code is 100% portable.

As side notes:


If identifiers are important to you, make sure you import them correctly, those may be important in the event of a later update
I do not think int(5) means what you think it means