Primary key or regular field in database for deleting a row?

Question

I want to erase a row in my database, I have 2 options; first to use a normal column to delete the row, second, the primary key?

I know that primary key is better, but why?

Answer 1

On MySql you can face strange locking behaviour in multiuser environment when deleting/updating rows using non-primary key columns.
Here is an example - two sessions trying to delete rows (autocommit is disabled).

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

mysql> create table test(
    ->   id int primary key,
    ->   val int
    -> );
Query OK, 0 rows affected (0.02 sec)

......

mysql> select * from test;
+----+------+
| id | val  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
+----+------+
6 rows in set (0.00 sec)

Now in session 1 we will delete row #5 using primary key

mysql> delete from test where id = 5;
Query OK, 1 row affected (0.00 sec)

and then in session 2 we delete row #2 using PK too

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id = 2;
Query OK, 1 row affected (0.00 sec)

Everything looks OK - row #5 was deleted by session 1 and row #2 deleted in session 2


And now look what will happen when we wil try to delete rows using non primary key:
Session 1

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where val = 5;
Query OK, 1 row affected (0.00 sec)

and session 2

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where val = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

Delete command in session 2 "hangs", and after a minute or so it throws an error: Lock wait timeout
Lets try to delete others rows:

mysql> delete from test where val = 4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from test where val = 6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

Session 1 deletes only row #5, and, logically, a lock shuould be placed only on record #5 beying deleted, but as you can see in these examples, when not using primary key, MySql placed locks on all rows of the whole table. So it is safer to delete rows using only primary key (at least on MySql).

Answer 2

Primary key is better because you are sure what row you are deleting: although technically you can update a primary key column, it is not a normal practice to do so. Other columns, however, are changeable, which could lead to situations like this:

• You have a table with a PK and another unique identifier, say, email
• You read a row with email [email protected], and decide to delete it
• The row gets modified concurrently, with the e-mail updated to [email protected]
• You execute the DELETE USER WHERE email='[email protected]'

The DELETE command does not delete anything, because the e-mail has been changed before you managed to run your command. Since PK is not supposed to change, this situation would not be possible under normal circumstances. Of course your code can detect that deletion did not happen, redo the read, and re-issue the command, but that is a lot of work compared to using a primary key.