Constraints

  • I want to do something like this but do not know the syntax

    CREATE UNIQUE NONCLUSTERED INDEX [IX_X] ON [dbo].[X]

    (

    [XName] ASC,

    [XUsersID] ASC,

    [IsFilter] CHECK(0) ASC

    )

    I only care about this constraint if isfilter = 0. Is that possible without a trigger?

  • You cannot add a check constraint to a column in a CREATE INDEX statement. Leave out the CHECK(0) and it will work.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I need the check on there. I have seen constraints that use udfs but I think that would be a bad idea...correct? So there is no way to do a unique constraint with those three column when isfilter = 0?

  • JKSQL (6/11/2009)


    I need the check on there. I have seen constraints that use udfs but I think that would be a bad idea...correct? So there is no way to do a unique constraint with those three column when isfilter = 0?

    OK, Let me try again: You can NOT put a check constraint on an index. They must go on columns in the table definition.

    Use the ALTER TABLE...ALTER COLUMN.. command to do this.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Can you give me an example of how I could make something like this request work? I am thinking a trigger can reinforce it but I do not think I can make this constraint. So the unique key would be XName,XId where isfilter = 0. Tell me if there is something else I can do

  • ???

    At this point, I cannot honestly say that I understand what you are trying to do?

    Are you trying to apply a Unique Constraint to only some of the records in the table?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • yes exactly.

    there would be one on filter = 0 and then one on filter = 1

    The table probably should have been split but there is already existing code and they have identical fields just the key has changed.

  • JKSQL (6/12/2009)


    yes exactly.

    there would be one on filter = 0 and then one on filter = 1

    The table probably should have been split but there is already existing code and they have identical fields just the key has changed.

    That makes no sense. How would that be any different that a Unique constraint on both that just included the IsFilter column?

    That is, how is what you want any different from this?:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_X] ON [dbo].[X]

    (

    [XName] ASC,

    [XUsersID] ASC,

    [IsFilter] ASC

    )

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • What you are requesting, a filtered index, is available under SQL Server 2008 but not under 2005

    The 2008 syntax for a filtered index would be:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_X] ON [dbo].[X]

    ([XName] ASC,[XUsersID] ASC)

    WHERE [IsFilter] = 0

    Under 2005, an alternative solution to a trigger, is to create a view that contains the appropriate where clause and then create a unique clustered index on the columns.

    CREATE TABLE dbo.Foo

    ( FooIdINTEGERNOT NULL

    , FooNameVARCHAR(255) NOT NULL

    , FooIndCHAR(1)NOT NULL

    , CONSTRAINT Foo_P PRIMARY KEY (FooId)

    , CONSTRAINT Foo_C_FooInd CHECK (FooInd IN ('Y','N' ) )

    )

    go

    CREATE VIEW FOO_FooNameUnique WITH SCHEMABINDING

    AS

    SELECTFoo.FooName

    FROMdbo.Foo

    WHEREFoo.FooInd = 'Y'

    WITH CHECK OPTION

    go

    CREATE UNIQUE CLUSTERED INDEX FOO_FooNameUnique_X

    ON dbo.FOO_FooNameUnique (FooName)

    go

    Test cases:

    delete from dbo.Foo

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)

    SELECT 1 , 'OK', 'Y' union all

    SELECT 2 , 'Duplicate', 'Y'

    -- Not a duplicate due to filter

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)

    SELECT 3 , 'Duplicate' , 'N'

    -- Not a duplicate due to filter

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

    SELECT 4 , 'Duplicate' , 'N'

    -- This is a duplicate and should cause an error

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)

    SELECT 5 , 'Duplicate', 'Y'

    The error message for the last test condition is:

    Msg 2601, Level 14, State 1, Line 1

    Cannot insert duplicate key row in object 'dbo.FOO_FooNameUnique' with unique index 'FOO_FooNameUnique_X'.

    The statement has been terminated.

    SQL = Scarcely Qualifies as a Language

  • Carl Federl (6/12/2009)


    What you are requesting, a filtered index, is available under SQL Server 2008 but not under 2005 ...

    Yeah, thats where I thought this was going too, Carl. But this statement by JK actually means the opposite:

    JKSQL (6/12/2009)


    yes exactly.

    there would be one on filter = 0 and then one on filter = 1

    Note that IsFilter appears to be a bit column (or used as a boolean), so 0 and 1 cover all of its possible values.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • In Carl's reply that is what I was looking for. I read about that on BOL. In Barry's last comment I would need two views so i could add the constraint both ways. Having a view that is used as a constraint has to be a bad idea correct? This will not be a big table probably right around 100-200 records. I should have put in the post what I had found and what I was looking for. Can you give me an example in 08 how this would look?

Viewing 11 posts - 1 through 10 (of 10 total)

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