"Keith G Hicks" <k**@comcast.net> wrote in message
news:u8PbQcrAGHA.4080@TK2MSFTNGP14.phx.gbl...
>I have table as follows:
>
> CREATE TABLE [dbo].[Table1] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ValType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MainVal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CodeVal] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED
> (
>  [ID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [IX_Table1_TypeValUnique] UNIQUE  NONCLUSTERED
> (
>  [ValType],
>  [MainVal]
> )  ON [PRIMARY]
> GO
>
> Data in the table as follows:
> ID     ValType    MainVal            CodeVal
> ---    --------    -----------            ---------
> 1       State         New York             NY
> 2       State         Arizona                 AZ
> 3       State         New Mexico         NM
> 4       Color         Green                 <NULL>
> 5       Color         Blue                    <NULL>
> 6       Color         Yellow                 <NULL>
> 7       Color         Red                      <NULL>
> 8       Color         Orange                 <NULL>
> 9       State         Ohio                         OH
> 10     Food         Tuna                     <NULL>
> 11     Food         Potatoe                 <NULL>
> 12     Food         Celery                     <NULL>
> 13     Food         Apple                     <NULL>
> 14     Food         Cake                     <NULL>
>
> When there is a value in the "CodeVal" column (which must allow nulls for
> some types) I need the combination of ValType & CodeVal to also be unique.
>
> I created a calculated column using the formula:
> (ltrim(rtrim(case when ([CodeVal] is null) then (str([ID])) else
> ([ValType]
> + [CodeVal]) end)))
> but when I tried to set the calculated column to be a unique constraint I
> get the error: "Cannot create index because the key column
> 'LkUpCodeUnique'
> is non-deterministic or imprecise."  Can anyone suggest a way to do this
> correctly?
>
> Thanks,
>
> Keith
>
>

"Keith G Hicks" <k**@comcast.net> wrote in message
news:uyexetrAGHA.808@TK2MSFTNGP10.phx.gbl...
> Cool. Thanks Tom. That almost works but I need all 3 columns when the
> CodeVal is used. I need ValType + MainVal to be unique all the time and I
> need CodeVal + ValType to be unique when the CodeVal is not null. I tried
> changing around what you have below to accomodate the MainVal also but I
> couldn't get the results I need. How can I do this?
>
> -keith
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:emdEFjrAGHA.3984@TK2MSFTNGP14.phx.gbl...
> Consider an indexed view:
>
> create view dbo.MyView
> with schemabinding
> as
> select
>    CodeVal, ValType
> from
>    dbo.Table1
> where
>    CodeVal is not null
> go
>
> create unique clustered index C_MyView on dbo.MyView (CodeVal, ValType)
> go
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada  t**@cips.ca
> www.pinpub.com
>
>
>

"Keith G Hicks" <k**@comcast.net> wrote in message
news:%23BKlS8rAGHA.208@tk2msftngp13.phx.gbl...
> Ah, so you don't actually ever "use" the view. It's just there to enforce
> something. Is that what you mean?
>
> Also, is it possible to use that same concept to require the MainVal AND
> the
> CodeVal when ValType = 'State'?  If so, how would I do that?
>
> -keith
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:eacaPxrAGHA.516@TK2MSFTNGP15.phx.gbl...
> You can select from the original table.  The purpose of the view is to
> enforce uniqueness.
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada  t**@cips.ca
>
>

"Keith G Hicks" <k**@comcast.net> wrote in message
news:emcQ2MsAGHA.516@TK2MSFTNGP15.phx.gbl...
> Sorry Tom, I don't understand. Using my previous example, how would you
> modify the code that creates dbo.MyView to require the CodeVal (not
> allowed
> to be null) when the ValType = 'State'?
>
> -keith
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:%23S3xHGsAGHA.2912@tk2msftngp13.phx.gbl...
> Yep, it's just to enforce uniqueness.  You can create an indexed view to
> filter on state.  For example, you could have one just for NY.
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON   Canada  t**@cips.ca
> www.pinpub.com
>
>
>