Say I have two tables, user
and comment
. They have table definitions that look like this:
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;
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;
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).
To this end, I've added deleted
so that I can SET deleted = 1
on a record. By listing everything with deleted = 0
by default, I can hide away all the deleted records until I need them.
So far so good.
The problem comes when:
user
.I want users to be able to edit their own usernames, so I shouldn't make username
the primary key, and we'll still have the same problem when deleting users.
Any thoughts?
Edit for clarification: Added following RedFilter's answer and comments below.
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.
This question is a thought experiment, with the user and comment tables just being examples. Still, username
wasn't the best one to use; RedFilter makes valid points about user identity, particularly when the records are presented in a public context.
Regarding "Why isn't username the primary key?": 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.
Add unique constraint on fields(username, deleted) Change field type for 'deleted' to INTEGER. During delete operation (it can be done in trigger, or in part of code where you need actually delete user) copy value of id field to deleted field. This approach allow you: keep unique names for active users (deleted = 0)
Dropping Foreign Key Constraints You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Exception: NDB Cluster requires an explicit unique key (or primary key) on the foreign key column.
A UNIQUE constraint can be referenced by a FOREIGN KEY constraint. When a UNIQUE constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure all values are unique.
Add unique constraint on fields(username, deleted) Change field type for 'deleted' to INTEGER.
During delete operation (it can be done in trigger, or in part of code where you need actually delete user) copy value of id field to deleted field.
This approach allow you:
Field 'Deleted' can't have only 2 value because the following scenario will not work:
Just keep the unique index or contraint on username
. You do not want new users to be able to use the deleted name, as not only could there be general confusion about identity, but if you are still showing the old posts from the deleted user, then they will mistakenly be understood to be posted by the new user with the same name.
When a new user registers, you would normally check to see if the name is in use before allowing registration to complete, so there should be no conflict here.
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With