Geek asks and answers

How to use only the year of a DateTime column in SQL Unique Constrain?

Im creates a unique restriction that allows you to use only the registry number per year, with this I mean that there can be more than one number 2 in the registry, but only if it was created in different years. I heard that you can make ir with a unique constraint, but I don’t know how, without having to create a column for a year, and another for a month, etc.

Inquiry

ALTER TABLE dbo.correspondencia_FFAA

ADD CONSTRAINT uk_correspondecia UNIQUE (num_corres, fecha_cre);

num_corres is the registry number, and fecha_cre is the creation date, but I only need a year, not the entire column, is this possible

thank

+3
source share
2 answers

, . - :

alter table registry add RegistryYear as year(RegistryDate);

create index registry_number_year on Registry(number, RegistryYear);
+5

, SQL Server "DATE" - .

, "DATE" DATETIME , PERSISTED . !

ALTER TABLE dbo.Entries
   ADD yearOnly as Year(CAST(CompositionDate AS DATE)) PERSISTED

CREATE UNIQUE INDEX UX_Entries ON Entries(yearOnly , Slug)
0

More articles:


All Articles