一组记录中的唯一约束，其中一些值相同

本文介绍了一组记录中的唯一约束，其中一些值相同的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 DBMS ：MS Sql Server 2005，Standard DBMS: MS Sql Server 2005, Standard我想建立一个表约束，值（表中的行共享特定列中的值）。这是可能的吗？I'd like to make a table constraint to have only one record have a particular value within a subset of the table (where the rows share a value in a particular column). Is this possible? 示例： 我在myTable中有一个非唯一的外键（fk1）一个名为isPrimary的位列，用于标记这个特殊的应用程序应该被特殊逻辑使用。Example:I have records in myTable which have a non-unique foreign key (fk1), and a bit column called isPrimary to mark out that this particular one should be used by our app for special logic.在抽象中，它看起来像这样：in the abstract, it looks like this:myTable-------------pk1 (int, not null)name (varchar(50), null)fk1 (int, not null)isPrimary (bit, not null)对于fk1的每个唯一值，我想确保对于isPrimary标志设置为1，有一个且只有一个记录。I want to ensure that there is one and only one record with the isPrimary flag set to 1, for each unique value of fk1.数据示例：这应该是合法的：Data example:This should be legal:pk1 name fk1 isPrimary---- ----- ----- ----------1 Bill 111 12 Tom 111 03 Dick 222 14 Harry 222 0但这应该 strong> be（多于一个，其中fk = 111）：But this should not be (more than one where fk=111):pk1 name fk1 isPrimary---- ----- ----- ----------1 Bill 111 12 Tom 111 13 Dick 222 14 Harry 222 0这两个都不应该（无其中fk = 222）：And neither should this (none where fk=222):pk1 name fk1 isPrimary---- ----- ----- ----------1 Bill 111 12 Tom 111 03 Dick 222 04 Harry 222 0有一种方法可以用表约束来做到这一点吗？Is there a way to do this with a table constraint? UPDATE I'现在已经和马丁·史密斯的答案一起去了，虽然我会在即将到来的版本中推送JohnFx的重构，因为它是最好的长期解决方案。但是，我想根据Raze2dust的答案发布更新的UDF，以便未来的读者决定更适合他们的需求。UPDATEI've gone with Martin Smith's answer for now, though I'll be pushing for JohnFx's refactor in an upcoming release, as it's the best long-term solution. However I wanted to post my updated UDF based on Raze2dust's answer, in case future readers decide that is a better fit for their needs.CREATE FUNCTION [dbo].[OneIsPrimaryPerFK1](@fk1 INT, @dummyIsPrimary BIT)RETURNS INTAS BEGIN DECLARE @retval INT; DECLARE @primarySum INT; SET @retval = 0; DECLARE @TempTable TABLE ( fk1 INT, PrimarySum INT)INSERT INTO @TempTable SELECT fk1, SUM(CAST(isPrimary AS INT)) AS PrimarySum FROM FacAdmin WHERE fk1 = @fk1 GROUP BY fk1; SELECT @primarySum = PrimarySum FROM @TempTable; IF(@primarySum=1) BEGIN SET @retval = 1 END RETURN @retvalEND;更改： 根据udf 的要求，使用@tempTable而不是 tempTable（在内存v。写入磁盘中） 组fk1值中的唯一性。 棘手还必须通过isPrimary， t 函数的逻辑必需，否则当isPrimary为更新时，SQL2005 优化器不会运行check 约束。 Used @tempTable rather thantempTable (in memory v. written to disk) as required by udfpassed @fk1 as a parameter so I can select for uniqueness within onegroup of fk1 values.tricky had to also pass isPrimary even though it isn'tnecessary for the logic of thefunction, otherwise the SQL2005optimizer will not run the checkconstraint when isPrimary isupdated. 推荐答案在检查约束中使用UDF可能会失败快照隔离或多行更新 。Using UDFs in check constraints can fail under snapshot isolation or multirow updates.假设你所有的fk1和pk1值都是正数，你可以用下面的定义创建一个计算列：Assuming that all your fk1 and pk1 values are currently (and will always be) positive you could create a computed column with the following definitionCASE WHEN isPrimary = 1 THEN fk1 ELSE -pk1 END然后为它添加一个唯一约束。或者如果不能做出该假设，那么也许then add a unique constraint to that. Or if that assumption can't be made then maybeCASE WHEN isPrimary = 0 THEN 1.0/pk1 ELSE fk1 END 这篇关于一组记录中的唯一约束，其中一些值相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！