MySQL table without primary key (best practice)

Question

I have a table users which contains several user information. I'd like to store the user specific permissions in a different table.

Do I need to have the primary key up_id in the user_permissinos table, even if it will not be used anywhere? I know it is always recommended to have a primary key in a table, but in this case it makes no sense for me. If I need to join both tables I will join up_of_user on user_id.

Table `user`
==========================
user_id int(11) PK AI NN
user_name varchar(50)
...


Table `user_permissions`
=======================================
up_id int(11) PK AI NN
up_of_user int(11) FK of user.user_id
up_edit_post tinyint(4) DEFAULT '0'

Answer 1

Please read the documentation of MySQL:

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.

From my experience and knowledge if you do not define your primary key the database will create an hidden primary key. In your situation I should kept the primary key.

Thanks @AaronDigulla for his explanation...:

Necessary? No. Used behind the scenes? Well, it's saved to disk and kept in the row cache, etc. Removing will slightly increase your performance (use a watch with millisecond precision to notice).

But ... the next time someone needs to create references to this table, they will curse you. If they are brave, they will add a PK (and wait for a long time for the DB to create the column). If they are not brave or dumb, they will start creating references using the business key (i.e. the data columns) which will cause a maintenance nightmare.

Conclusion: Since the cost of having a PK (even if it's not used ATM) is so small, let it be.

Answer 2

InnoDB has 3 choices of a PK: In order,

1. Explicit PRIMARY KEY(...) -- Best practice: Always do this.
2. The first UNIQUE(...) with all NON NULL columns. -- Sloppier than #1.
3. A hidden 6-byte ~integer. -- Since it is hidden, it makes maintenance on the table difficult.

AUTO_INCREMENT versus "natural" PK:

First rule: If you have a natural key, use it rather than adding an artificial id. About 2/3 of the tables I have created have a "natural" PK, sometimes 'composite' (multiple columns).

Second rule: If you have more than one secondary index, it may take less space to use an AI PK. This is because every secondary key includes a copy of the PK.

Third rule: For only one secondary index, it is a toss up.

Examples

• Many to many mapping
• Optimizing indexes for wp_postmeta

Old wives' tales aka "Fake news"

• "A bulky PK is slow" -- maybe, maybe not.
• "A VARCHAR PK is slow" -- not by much.

And if you have other savings (eg, getting rid of a column), that may override the old wives' tales.

Your example

Toss up_id and promote user_name to be the PK.

It sounds like user and user_permissions are in a 1:1 relationship? It is very rare to have two tables with the same PK. Normally the two tables should be combined into one.