软删除与唯一索引冲突的解决方案

我们现在删除数据库表数据的时候大都会用软删除，或者用某个字段标记为无效，但如果该表某个字段需要有一个唯一索引，但是无效或软删除的记录不应该在唯一索引范围内。

说了一大堆，举个例子，比如有一张user表

CREATE TABLE USER_TEST (
	id BIGINT NOT NULL AUTO_INCREMENT,
	user_name VARCHAR (50) NOT NULL COMMENT "用户名",
	pwd VARCHAR (50) NOT NULL DEFAULT "" COMMENT "密码",
	id_card VARCHAR (18) NULL DEFAULT "" COMMENT "身份证",
	is_delete TINYINT NOT NULL COMMENT "是否已删除：1，是  0否",
	PRIMARY KEY (id),
	UNIQUE KEY un_id_card (id_card)
)

身份证要求全表唯一，但是如果某个用户被删掉了（即is_delete=1)，那么这个人的身份证需要可以被别人使用，但就目前表结构是不可以的，因为再插入的时候会与被删除的用户身份证唯一索引冲突。

那怎么办呢，把UNIQUE KEY un_id_card (id_card)改成联合索引唯一约束UNIQUE KEY un_id_card (id_card,is_delete)可以吗？好像可以解决问题，但是第二个用户再删除后，第三个用户用这个身份证又有同样的问题了。

其实再改造一步就可以了。

我们把is_delete设为全局增长的一个id，或者删除时的时间戳，或者一个uuid，这样再加上UNIQUE KEY un_id_card (id_card,is_delete)这个联合唯一约束就可以了。当然前提是需要改一下is_delete字段的类型，未删除的都是0，其他的非0，这样唯一索引不就可以达到只是未删除的效果了吗？