SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Constraints


Constraints

Author
Message
JKSQL
JKSQL
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3658 Visits: 702
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?
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69014 Visits: 9519
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."
JKSQL
JKSQL
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3658 Visits: 702
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?
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69014 Visits: 9519
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."
JKSQL
JKSQL
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3658 Visits: 702
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69014 Visits: 9519
???

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."
JKSQL
JKSQL
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3658 Visits: 702
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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69014 Visits: 9519
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."
Carl Federl
Carl Federl
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12824 Visits: 4355
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69014 Visits: 9519
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search