MySQL primary key that I never select

Quite often I encounter situation like this:

table `user_adress`
+----------+-------------+--------------+---------+
|adress_id | user_id     | adress_type  |adress   |
+----------+-------------+--------------+---------+
|        1 |           1 | home         |adressXXX|
|        2 |           2 | home         |adressXXX|
|        3 |           3 | home         |adressXXX|
|        4 |           1 | work         |adressXXX|
|        5 |           2 | work         |adressXXX|
|        6 |           1 | second_home  |adressXXX|
+----------+-------------+--------------+---------+

If I want to use it, I'm using queries like this:

SELECT `adress` FROM `user_adress` WHERE `user_id`=1;

Seems quite normal, but the thing is, that I use "useless" adress_id column, that has no other purpouse but to be an primary key with autoincrement just for the sake of having an primary key in MySQL table. I never use or need this number. So I figured out that I should not use primary key in my table at all, remove totally adress_id, and set INDEX (without unique) at user_id column. That seems to be good - or am I wrong?

I have some doubts, because as much as I'm reading, everywhere I see advices, that every table should, or even need to have primary key. But why? Perhaps my database is badly designed if I allowed this to happen, but looking on my extreamly simple example table - I can't imagine how this could be the case in every situation, especially in such simple cases. I deffinetly missunderstanded some simple, basic rules about creating tables and properly indexing them - where is the hole in my toughts?

Purely based on your table structure, I would say that your primary key is incorrect.

Instead, it looks like your primary should be:

PRIMARY KEY (user_id, address_type)

You are correct that every table should have a primary key ideally, but primary keys can be over multiple fields.

It is still sometimes easier to have a simple auto-incrementing id as your primary key. The Innodb storage engine will actually do this secretly in an invisible field.

Maybe in your limited example it's not needed, but in a lot of real-world cases it can just make it easier to work with the data. In that sense I would say that having an artificial auto-incrementing primary key is not a best practice from an academic standpoint, but it can be good idea from a 'real world, operational, and MySQL admin' perspective.

There's also ORM systems out there that simply require this (bad as that is).

Because MySQL works faster with integers, the data type of the primary key column should be the integer e.g., INT, BIGINT.And you should ensure sure that value ranges of the integer type for the primary key are sufficient for storing all possible rows that the table may have.

As is evident in your data the primary key allow the access directly to a single row without any problem or ambiguity .. (expecially for delete or updated)

this is specifically the purpose of a primary key ..

di the fact you could need join this table to others table by user_id

and index (not unique ) on user_id

create index  myidx on mytable(user_id)

is really useful for faster join allow a direct access only at the rows related to a single user_id

In this way no primary key will be automatically generated, but the creation of tables without a primary key will fail. MySQL 8.0 introduced a better variable: sql_require_primary_key. It simply disallows to create tables without a primary key, or drop a primary key from an existing table.

It's true that a relational database table needs a primary key.

But it all comes down to the definition of a primary key. A primary key is NOT necessarily a single integer column that auto-increments.

A primary key is any column or set of multiple columns that can uniquely identify every row. In your case, the combination of user_id and address_type can do this (as Evert posted already).

So if you make your table like this:

CREATE TABLE user_address (
  user_id INT NOT NULL,
  address_type varchar(10) NOT NULL,
  address TEXT NOT NULL,
  PRIMARY KEY (user_id, address_type)
);

Then you can update or delete one specific row at a time like this:

UPDATE user_address SET ...
WHERE user_id = ? AND address_type = ?;

Some people feel that it's more convenient to enforce a convention that every table should have a single integer column as its primary key. They even may insist that the column must be called id for the sake of consistency.

There's some advantage in consistency, but on the other hand, it's kind of brainless to insist on that convention even when it's not helpful.

The primary key should never be NULL. For example, if a particular student does not provide email address or phone number, we cannot use either of them as our primary key. In order make this selection of primary key's more easier, we can actually create/add an artificial attribute (column) from our side, that will be defined as unique.

In MySQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement. You use the ALTER TABLE statement in MySQL to drop, disable or enable a primary key. Create Primary Key - Using CREATE TABLE statement You can create a primary key in MySQL with the CREATE TABLE statement.

SELECT ..) a MyISAM table that have a PRIMARY key, it is much faster to insert the new rows in the primary key order: The insertion will be faster: because the primary key cannot disabled, every insertion will also updates the primary key. In a case the rows are inserted in the primary key order, there will be no redundant disk scans.

use it, MySQL ignores primary-key or unique-key violations and continues processing with the next row. See the section for the statement that you are using (Section 13.2.6, “INSERT Statement”, Section 13.2.13, “UPDATE Statement”, and so forth).

Comments
  • How would you delete user 1's home address without removing their work and second_home addresses? Even if you have a plan for that, some frameworks/environments cannot operate to their full capacity if their presumption of a primary key is not met.
  • You are absolutly right, I expanded the example table - my mistake!
  • user_id, address_type could be seen as a candidate primary key, but if someone had three home addresses you would have to add a new address type. Either way, you'd still end up with a primary key; it's just with the composite one, anything that references the address (perhaps a bill or ship to address) will now have to be joined/queried on two values instead of one.
  • Of note: There may be major performance impacts with InnoDB when using a non-incrementing primary key (or no primary key): kccoder.com/mysql/uuid-vs-int-insert-performance Some replication systems like Galera need one, too.
  • @ceejayoz - That links to a bad test. None of hist tables had a PRIMARY KEY, which is essential to InnoDB. And the graphs head for the sky too fast. And he did not say what value was used for innodb_buffer_pool_size. But the conclusion is somewhat correct: UUIDs suck for performance, in some situations.
  • I guess that You are right. I edited my example table - it's still fictional, but it does represent what I do have in my database, and now Your suggestion to use PK on user_id and adress_type(name changed) does have a lot of sense. The things that You are saying that such, as I called before, "useless" PK table is indeed used in background is quite interesting. Will this in fact work in my example, or should I add INDEX on user_id column? Or should I have both? Can I overextend the usage of pure, non-unique INDEX columns if i know that I'll often use it in my WHERE parts of queries?
  • @Zorann I changed my PRIMARY KEY to match your new field type. You still don't need an artificial PK, but in your case it does still make sense to have a PK over both user_id and address_type. If you have that, you don't need an INDEX on user_id as the PK will be used.