Post reply

UNIQUE Constraint Help please

  • July 19, 2016 at 2:49 am

    #328543

    HI I need to add a unique constraint on a table #temp.

    The following two columns are amongst some of its columns

    TIMESTAMP QUESEARCHPARAM

    ----------------------- ------------ ------------- ----------------------

    2016-07-18 08:42:37.260 example1

    2016-07-18 09:29:23.040 example2

    2016-07-18 10:02:27.330 example3

    I need to add a constraint so the there are no duplicate QUESEARCHPARAM within the last two hours.

    alter table #temp

    ADD CONSTRAINT test_CONSTRAINT

    UNIQUE (QUESEARCHPARAM,QUETIMESTAMP)

    The above is obviously no use as it doesn't take 2 hours from the TIMESTAMP Everytime I try and play with QUETIMESTAMP inside the brackets of the UNIQUE constrain it throws errors - Any ideas please??

  • July 19, 2016 at 3:17 am

    #1890789

    Have you tried a unique filtered index?

    John

  • July 19, 2016 at 3:26 am

    #1890790

    Filtered index won't work in this case, as the filter on an index has to be deterministic. Can't filter for timestamp > dateadd(hh,-2,getdate())

    Msg 10735, Level 15, State 1, Line 8

    Incorrect WHERE clause for filtered index

    hpickrell: You'll probably need a trigger to check for this and roll back invalid inserts/updates. Be careful writing such trigger and make sure it's efficient and allows for any number of rows in the inserted table.

    It's easy to do no duplicates within a fixed 2-hour window, eg no duplicates allowed between 00:00:00 and 02:00:00, no duplicates allowed between 02:00:00 and 04:00:00, but that would allow there to be the same parameter value at 03:50:00 and 04:10:00, which I suspect is not what you want.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2016 at 3:26 am

    #1890791

    ok thanks - ill give triggers a go

  • July 20, 2016 at 12:45 pm

    #1891124

    You could use a standard unique constraint if you continually deleted searches 2 hours or more old from the table. For example, have a job that deletes rows every minute (or two or whatever). You'd very likely want to cluster the table first on QUETIMESTAMP, to make the DELETEs more efficient overall:

    CREATE UNIQUE CLUSTERED INDEX temp__CL ON #temp ( QUETIMESTAMP, QUESEARCHPARAM ) WITH ( FILLFACTOR = 99 );

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • July 21, 2016 at 12:03 am

    #1891213

    Thanks for you help - I'll give it a try

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply