Search code examples
sqlprimary-keysql-delete

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

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?

Solution

  • 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.