我可以有条件地强制执行唯一性约束吗? [英] Can I conditionally enforce a uniqueness constraint?

问题描述

我的数据库包含一个用户表.每个活跃用户都有一个唯一的用户名.我希望能够停用用户并释放他们正在使用的用户名，但将它们保留在同一个表中.

My database contains a table of users. Every active user has a unique username. I'd like to be able to deactivate a user and free up the username they're using, but keep them in the same table.

有没有办法仅有条件地强制执行唯一性约束?

Is there a way to only conditionally enforce the uniqueness constraint?

推荐答案

添加另一个名为 isactive 之类的列.在 (username, isactive) 上创建唯一约束.

Add another column called something like isactive. The create a unique constraint on (username, isactive).

然后您可以同时拥有活动和非活动用户名.您将无法拥有两个活动用户名.

Then you can have both an active and inactive user name at the same time. You will not be able to have two active user names.

如果您想要多个非活动名称，请使用 NULL 作为 isactive 的值.NULL 值可以在唯一索引中重复.

If you want multiple inactive names, use NULL for the value of isactive. NULL values can be repeated in a unique index.

这篇关于我可以有条件地强制执行唯一性约束吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！