CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Amirhossein
Amirhossein

Reputation: 1251

Check the number of digits or their range

I tried to create a table for save Tradeshows and I need to save Year and Month into different columns.

I have a problem with my back-end and front-end developers for check data. I need to check years is 4 number and month between 1-12. Speed is important to me.

CREATE TABLE [dbo].[TradeShows](
    [TradeShowsID] [bigint] IDENTITY(1,1) NOT NULL,
    [TradeShowsName] [nvarchar](100) NULL,
    [YearAttended] [smallint] NULL,
    [MonthAttended] [tinyint] NULL,
 CONSTRAINT [PK_TradeShows] PRIMARY KEY CLUSTERED 
(
    [TradeShowsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Just use check constraints:

alter table TradeShows add constraint chk_tradeshows_year
    check (YearAttended >= 2000 and YearAttended < 2100);

alter table TradeShows add constraint chk_tradeshows_month
    check (MonthAttended >= 1 and MonthAttended <= 12);

Or, you could get fancy and validate that the two together form a valid date:

alter table TradeShows add constraint chk_tradeshows_year_month
    check (try_convert(date, concat('-', YearAttended, MonthAttended, '-01')) is not null);

Upvotes: 1

Related Questions