Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Constraints Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 1:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:32 AM
Points: 415, Visits: 595
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?
Post #733300
Posted Thursday, June 11, 2009 2:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
You cannot add a check constraint to a column in a CREATE INDEX statement. Leave out the CHECK(0) and it will work.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #733357
Posted Thursday, June 11, 2009 3:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:32 AM
Points: 415, Visits: 595
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?
Post #733367
Posted Thursday, June 11, 2009 3:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #733376
Posted Friday, June 12, 2009 8:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:32 AM
Points: 415, Visits: 595
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
Post #733893
Posted Friday, June 12, 2009 8:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
???

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?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #733909
Posted Friday, June 12, 2009 8:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:32 AM
Points: 415, Visits: 595
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.
Post #733917
Posted Friday, June 12, 2009 8:52 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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
)



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #733927
Posted Friday, June 12, 2009 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:46 PM
Points: 2,285, Visits: 4,218
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
( FooId INTEGER NOT NULL
, FooName VARCHAR(255) NOT NULL
, FooInd CHAR(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
SELECT Foo.FooName
FROM dbo.Foo
WHERE Foo.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
Post #733928
Posted Friday, June 12, 2009 9:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #733934
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse