title: MySQL with Soft-Deletion, Unique Key and Foreign Key Constraints
body: <p>Say I have two tables, <code>user</code> and <code>comment</code>. They have table definitions that look like this:</p>
  
  <pre><code>CREATE TABLE `user` (
    `id`       INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `deleted`  TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`username`)
  ) ENGINE=InnoDB;</code></pre>
  
  <pre><code>CREATE TABLE `comment` (
    `id`      INTEGER NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER NOT NULL,
    `comment` TEXT,
    `deleted` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_comment_user_id` FOREIGN KEY (`user_id`)
      REFERENCES `user` (`id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE
  ) ENGINE=InnoDB;</code></pre>
  
  <p>This is great for enforcing data integrity and all that, but I want to be able to "delete" a user and keep all its comments (for reference's sake).</p>
  
  <p>To this end, I've added <code>deleted</code> so that I can <code>SET deleted = 1</code> on a record. By listing everything with <code>deleted = 0</code> by default, I can hide away all the deleted records until I need them.</p>
  
  <p>So far so good.</p>
  
  <p>The problem comes when:</p>
  
  <ul>
  <li>A user signs up with a username (say, "Sam"),</li>
  <li>I soft-delete that user (for unrelated reasons), and</li>
  <li>Someone else comes along to sign up as Sam, and suddenly we've violated the UNIQUE constraint on <code>user</code>.</li>
  </ul>
  
  <p>I want users to be able to edit their own usernames, so I shouldn't make <code>username</code> the primary key, and we'll still have the same problem when deleting users.</p>
  
  <p>Any thoughts?</p>
  
  <p><strong>Edit for clarification:</strong> Added following RedFilter's answer and comments below.</p>
  
  <p>I'm concerned with the case where the "deleted" users and comments are not visible to the public, but are visible only administrators, or are kept for the purpose of calculating statistics.</p>
  
  <p>This question is a thought experiment, with the user and comment tables just being examples. Still, <code>username</code> wasn't the best one to use; RedFilter makes valid points about user identity, particularly when the records are presented in a public context.</p>
  
  <p>Regarding "<em>Why isn't username the primary key?</em>": this is just an example, but if I apply this to a real problem I'll be needing to work within the constraints of an existing system that assumes the existence of a surrogate primary key.</p>