SQL 唯一约束
我有一个表 Person ，其中包含 Code(varchar)、Name(varchar) 和 Active(bit)字段。当我搜索 Person 时，仅返回活动记录，当我删除 Person 时，我设置 Active = false 以便它不显示在我的搜索中。
问题是我在 Code 和 Name 字段中添加了唯一约束，因此每个 Person 只能有一个。现在的问题是，用户在搜索中看不到 Person（因为他们的活动为 false），并尝试添加相同的 Person，但唯一约束阻止他们执行此操作所以。
有没有办法保留唯一约束，并可能添加对 Active 的检查，以便我仍然可以添加该 Person（如果之前设置为 active = false）？我知道这里的简单解决方案是完全删除记录，但出于数据目的，我需要保留记录而不是删除它，因此只需设置 active = false。
谢谢
I have a table Person with a Code(varchar), a Name(varchar) and Active(bit) fields. When I search for a Person only active records are returned, and when I delete a Person I set Active = false so that it does not show up in my Search.
The problem is I've added a Unique Constraint on the Code and Name fields, so that there can only be one of each Person. Problem is now the User doesn't see the Person in the Search (coz their active is false) and tries to add that same Person but the Unique constraint stops them from doing so.
Is there a way to keep the Unique constraint and maybe add a check to Active so that I can still add that Person if it was previously set as active = false? I know the easy solution here is to just delete the record completely but for data purposes I need to keep the record and not delete it hence only setting active = false.
thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不会。如果您对一个或多个字段应用唯一约束，则可以防止该字段出现任何重复数据。
理论上，您可以对所有三个字段应用唯一约束，但是如果表中存在同一个人并且已经“删除”（将 active 设置为 false），这将阻止您“删除”某个人。
如果这是您关心的问题，我会亲自存档数据，而不是使用“活动”字段。创建一个新表并在其中插入删除以保存数据并将其从现有的“Person”表中删除。
No. If you apply a unique constraint to a field (or fields) then it is prevented from having any duplicate data.
Theoretically, you could apply the unique constraint over all three of your fields, but that would prevent you from being able to 'delete' a person if the same person existed in the table and was already 'deleted' (having active set to false).
I would personally archive the data rather than having an 'active' field if this is your concern. Create a new table and insert the deletes in there to save the data and remove them from the existing 'Person' table.
怎么样...
删除约束，并对表进行计数。在插入之前只需对表进行计数，以确保没有重复记录。
亲切的问候
How about...
Remove the constraint, and do a count on the table. Before the insert just do a count on the table, to ensure that there are no duplicate records.
kind regards