cast inside check constraint???

8 Dec 2005 9:59 PM

sqlster

I get an error when I try to do cast inside check constraint like the
following:

CREATE TABLE [dbo].[mytbl] (
    [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
    [month] [int] NOT NULL ,
    [year] [int] NULL
)
GO

ALTER TABLE [dbo].[mytbl] ADD
    CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
    (
        [mytblid],
        cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
    )

Please help.

8 Dec 2005 10:16 PM

Alexander Kuznetsov

why can't you just use a unique constraint on (mytblid, month, year)?

Anyway, you can use a computed column

CREATE TABLE [dbo].[mytbl] (
        [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
        [month] [int] NOT NULL ,
        [year] [int] NULL,
    [mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
varchar(4))
)
GO

ALTER TABLE [dbo].[mytbl] ADD
        CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
        (
                [mytblid], [mon_slash_yr]
        )
go
drop table [dbo].[mytbl]
go

8 Dec 2005 10:24 PM

sqlster

Thanks

Show quote

"Alexander Kuznetsov" wrote:

> why can't you just use a unique constraint on (mytblid, month, year)?
>
> Anyway, you can use a computed column
>
> CREATE TABLE [dbo].[mytbl] (
>         [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
>         [month] [int] NOT NULL ,
>         [year] [int] NULL,
>     [mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
> varchar(4))
> )
> GO
>
>
> ALTER TABLE [dbo].[mytbl] ADD
>         CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
>         (
>                 [mytblid], [mon_slash_yr]
>         )
> go
> drop table [dbo].[mytbl]
> go
>
>

8 Dec 2005 10:24 PM

Gert-Jan Strik

This is not a check constraint. You specified a unique constraint. Casts
are allowed in check constraints. Unique constraints can only contain
column names.

Please specify what you want to achieve, because unless you are using
IDENTITY_INSERT ON, all rows in this table will be unique regardless of
the month/year setting.

Gert-Jan

sqlster wrote:
Show quote

>
> I get an error when I try to do cast inside check constraint like the
> following:
>
> CREATE TABLE [dbo].[mytbl] (
>         [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
>         [month] [int] NOT NULL ,
>         [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
>         CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
>         (
>                 [mytblid],
>                 cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
>         )
>
> Please help.

8 Dec 2005 10:32 PM

Trey Walpole

that's a unique constraint, not a check constraint

just make it on all three columns

ALTER TABLE [dbo].[mytbl] ADD
    CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
    (
        [mytblid],
        [month],
        [year]
    )

sqlster wrote:
Show quote

> I get an error when I try to do cast inside check constraint like the
> following:
>
> CREATE TABLE [dbo].[mytbl] (
>     [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
>     [month] [int] NOT NULL ,
>     [year] [int] NULL
> )
> GO
>
>
> ALTER TABLE [dbo].[mytbl] ADD
>     CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
>     (
>         [mytblid],
>         cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
>     )
>
> Please help.

8 Dec 2005 11:01 PM

David Portas

sqlster wrote:
Show quote

> I get an error when I try to do cast inside check constraint like the
> following:
>
> CREATE TABLE [dbo].[mytbl] (
>     [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
>     [month] [int] NOT NULL ,
>     [year] [int] NULL
> )
> GO
>
>
> ALTER TABLE [dbo].[mytbl] ADD
>     CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
>     (
>         [mytblid],
>         cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
>     )
>
> Please help.

I guess this is what you need:

CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1,1) NOT NULL
CONSTRAINT pk_mytbl PRIMARY KEY ,
[month] [int] NOT NULL ,
[year] [int] NOT NULL
)
GO

ALTER TABLE dbo.mytbl
ADD CONSTRAINT myconstraint
UNIQUE NONCLUSTERED ([month], [year])
GO

--
David Portas
SQL Server MVP
--