tempTable (in memory v. written to disk) as required by udf

Question

<>DBMS>: 2005年,MS Sql服务器

我在表一栏中仅限一个记录具有特殊价值(各行在某一栏中具有价值)。这是可能的吗?

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.

它抽象地看待这一点:

myTable
-------------
pk1       (int, not null)
name      (varchar(50), null)
fk1       (int, not null)
isPrimary (bit, not null)

我想确保有one和只有一个记录,每个独特价值为 fk1,其法定旗帜为1。

Data example: This should be legal:

pk1     name     fk1    isPrimary
----    -----    -----  ----------
1       Bill     111    1
2       Tom      111    0
3       Dick     222    1
4       Harry    222    0

但是,这应当<>而不是(超过1k=111):

pk1     name     fk1    isPrimary
----    -----    -----  ----------
1       Bill     111    1
2       Tom      111    1
3       Dick     222    1
4       Harry    222    0

这两种情况(在fk=222的地方)都不应如此。

pk1     name     fk1    isPrimary
----    -----    -----  ----------
1       Bill     111    1
2       Tom      111    0
3       Dick     222    0
4       Harry    222    0

是否有办法用表上的限制来做到这一点?

UPDATE I 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 INT
AS 
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 @retval
END;

变化:

Used @tempTable rather than
tempTable (in memory v. written to disk) as required by udf
passed @fk1 as a parameter so I can select for uniqueness within one group of fk1 values.
tricky had to also pass isPrimary even though it isn t necessary for the logic of the function, otherwise the SQL2005 optimizer will not run the check constraint when isPrimary is updated.

Answer 1

利用UDFs进行核查,可在以下网址下失败:snapshotlate或yrogerson/archive/2008/02/07/using-a-udf-in-a-check-constraint-to-check-validity-of-hist-wind-ow= nowinder=

假设你的所有k1和pk1数值目前(而且永远是)是肯定的,你可以建立一个计算栏,定义如下:

CASE WHEN isPrimary = 1 THEN fk1 ELSE -pk1 END

之后,加上一个独特的制约因素。或者如果这种假设无法作出,则可能

CASE WHEN isPrimary = 0 THEN 1.0/pk1 ELSE fk1 END

Answer 2

自我对第一个问题提出批评以来,我们开始作出新的回答。

你可以重新思考你的桌子设计,避免使你成为执行你业务规则的制约因素,从而解决这个问题。

如何从MyTable中删除大专栏,在提到主要人物的另一张表格中增加一栏?

这样,结构本身就可以强制规定,个人在家庭预算表中只有1个入境点。

Answer 3

2005年5月5日至5日,日内瓦然而,2005年解决该问题的办法有几个:

Create an Indexed view that filters for IsPrimary = 1 and add a unique index to that view.
Create a trigger where you ensure only one can be primary.
Encapsulate your logic into a stored proc and force users to go through the stored proc to insert or update from your table.

Answer 4

您可以尝试创建一种功能,然后使用检查限制:

CREATE FUNCTION ChkFn()
RETURNS INT
AS 
BEGIN
   DECLARE @retval INT
   DECLARE @distinct INT
   DECLARE @top INT
   SET @retval = 0
   SELECT fk1 AS ForeignKey, SUM(isPrimary) AS PrimarySum
    INTO #TempTable 
    FROM myTable
    GROUP BY fk1
   SELECT @distinct = COUNT(DISTINCT(PrimarySum)) FROM #TempTable
   SELECT @top = top PrimarySum FROM #TempTable
   IF(@distinct=1 AND @top=1)
    BEGIN
    @retval = 1
    END
   RETURN @retval
END;
GO

ALTER TABLE myTable
ADD CONSTRAINT chkFkPk CHECK (dbo.ChekFn() = 1 );
GO

问它,让我知道它是否奏效。然而,这并不令人信服。

tempTable (in memory v. written to disk) as required by udf

友情链接