cast inside check constraint??

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.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|||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:
> 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.|||Thanks
"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
>|||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:
> 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.|||sqlster wrote:
> 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
--