Professional Web Applications Themes

Best way to create this constraint? - Microsoft SQL / MS SQL Server

I need to enforce a simple validation rule on my table. The solution I came up works, but seems complex. Im hopping there is a better way to do this (reduced to the simplest case): I have table T, with fields: id (int key) fSelected (bit) //note fSelected is indexed I want to enforce that only one object at most can have fSelected = 1. That is, either 0 or 1 objects in table T can have fSelected = 1. The way I did it was to create 2 user-defined functions, and 1 constraint, as follows: Constraint on table T: ...

  1. #1

    Default Best way to create this constraint?

    I need to enforce a simple validation rule on my table. The solution I came
    up works, but seems complex. Im hopping there is a better way to do this
    (reduced to the simplest case):

    I have table T, with fields:
    id (int key)
    fSelected (bit) //note fSelected is indexed

    I want to enforce that only one object at most can have fSelected = 1.
    That is, either 0 or 1 objects in table T can have fSelected = 1.
    The way I did it was to create 2 user-defined functions, and 1 constraint,
    as follows:

    Constraint on table T:

    ([fSelected] = 0 or [dbo].[CountSelected]() = 0 or [dbo].[IdSelected]() =
    [id])

    //CountSelected
    CREATE FUNCTION dbo.CountSelected
    (
    )
    RETURNS int
    AS
    BEGIN
    DECLARE cTotal int

    Select cTotal = Count(*) From T
    Where fSelected = 1
    RETURN (cTotal)
    END

    //IdSelected
    CREATE FUNCTION dbo.IdSelected
    (
    )
    RETURNS int
    AS
    BEGIN
    DECLARE idRet int

    Select idRet = id From T
    Where fSelected = 1
    RETURN (idRet)
    END

    Notes: I tried doing it with a single function, but MS SQL doesnt let me
    create a temporary table inside a function, so I had to create 2 functions.
    In the worst case it needs to access the index twice (first to get the
    count, and second to go get the id of fSelected row. Ideally I want to only
    access the index once. In theory it should be possible, because fSelected is
    indexed and values are 0 or 1, in theory it should right away find the item
    with "1" and realize there is only 1 row in the index.

    Now, because the trigger first checks for (fSelected = 0), the trigger
    should have neglegible performance loss for the most common case of
    fSelected =0, and thus the 2 functions will not get evaluated. Am I correct
    on this? (that is, the OR evaluation is lazy, right?)


    Zig Mandel Guest

  2. #2

    Default Re: Best way to create this constraint?

    You can do it a bit simpler in this case:

    CREATE FUNCTION dbo.SelectedExists
    (
    )
    RETURNS int
    AS
    BEGIN
    DECLARE cExists int

    IF EXISTS (SELECT * FROM T Where fSelected = 1)
    THEN cExists =1
    ELSE cExists =0

    RETURN (cExists )
    END
    GO
    ALTER TABLE T ADD CONSTRAINT only_one_selected CHECK (dbo.SelectedExists -
    fSelected = 0)
    GO

    IIRC OR is only "lazy" if the equation can be resolved at compile time as
    you can see in this example:

    IF 1=1 OR EXISTS(SELECT * FROM employees)
    PRINT 'Hello'

    DECLARE i INT
    SET i = 1
    IF 1= i OR EXISTS(SELECT * FROM employees)
    PRINT 'Hello'

    and you are comparing with a column here.
    You can compare for yourself however if the constraint as I have written it
    above performs the same as if you change it to CHECK( fSelected = 0 OR
    dbo.SelectedExists - fSelected = 0)


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Zig Mandel" <nonehotmail.com> wrote in message
    news:uT16Vq5PDHA.1608TK2MSFTNGP11.phx.gbl...
    > I need to enforce a simple validation rule on my table. The solution I
    came
    > up works, but seems complex. Im hopping there is a better way to do this
    > (reduced to the simplest case):
    >
    > I have table T, with fields:
    > id (int key)
    > fSelected (bit) //note fSelected is indexed
    >
    > I want to enforce that only one object at most can have fSelected = 1.
    > That is, either 0 or 1 objects in table T can have fSelected = 1.
    > The way I did it was to create 2 user-defined functions, and 1 constraint,
    > as follows:
    >
    > Constraint on table T:
    >
    > ([fSelected] = 0 or [dbo].[CountSelected]() = 0 or [dbo].[IdSelected]() =
    > [id])
    >
    > //CountSelected
    > CREATE FUNCTION dbo.CountSelected
    > (
    > )
    > RETURNS int
    > AS
    > BEGIN
    > DECLARE cTotal int
    >
    > Select cTotal = Count(*) From T
    > Where fSelected = 1
    > RETURN (cTotal)
    > END
    >
    > //IdSelected
    > CREATE FUNCTION dbo.IdSelected
    > (
    > )
    > RETURNS int
    > AS
    > BEGIN
    > DECLARE idRet int
    >
    > Select idRet = id From T
    > Where fSelected = 1
    > RETURN (idRet)
    > END
    >
    > Notes: I tried doing it with a single function, but MS SQL doesnt let me
    > create a temporary table inside a function, so I had to create 2
    functions.
    > In the worst case it needs to access the index twice (first to get the
    > count, and second to go get the id of fSelected row. Ideally I want to
    only
    > access the index once. In theory it should be possible, because fSelected
    is
    > indexed and values are 0 or 1, in theory it should right away find the
    item
    > with "1" and realize there is only 1 row in the index.
    >
    > Now, because the trigger first checks for (fSelected = 0), the trigger
    > should have neglegible performance loss for the most common case of
    > fSelected =0, and thus the 2 functions will not get evaluated. Am I
    correct
    > on this? (that is, the OR evaluation is lazy, right?)
    >
    >

    Jacco Schalkwijk Guest

Similar Threads

  1. Replies: 11
    Last Post: June 2nd, 04:01 AM
  2. Adding Constraint
    By wallace reis in forum MySQL
    Replies: 2
    Last Post: December 15th, 07:27 PM
  3. RAISEERROR from within constraint?
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 06:48 AM
  4. Conditional Constraint?
    By Howard J. Rogers in forum Oracle Server
    Replies: 9
    Last Post: December 24th, 02:52 PM
  5. Replies: 1
    Last Post: December 9th, 03:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139