Reputation: 67
Im creating a Unique constraint, that only allows to use the a Registry number per year, with this i mean that can exist more that one number 2, in the registry but only if this were created on different years. I have heard that it is possible to do ir with the Unique constraint but i do not know how, without needing to create a column for year and another for month, and ect.
Query
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 the year not the whole column, is it possible
Thanks
Upvotes: 3
Views: 2280
Reputation: 28413
Well, in SQL Server, there's a datatype called "DATE" - you could use that column and create an index on that.
You could of course also add a computed column of type "DATE" to your table and just fill the date portion of the DATETIME column into that computed column, make it PERSISTED, and index it. Should work just fine!
ALTER TABLE dbo.Entries
ADD yearOnly as Year(CAST(CompositionDate AS DATE)) PERSISTED
CREATE UNIQUE INDEX UX_Entries ON Entries(yearOnly , Slug)
Upvotes: 0
Reputation: 1270021
Use a computed column and then created the index. Something like:
alter table registry add RegistryYear as year(RegistryDate);
create index registry_number_year on Registry(number, RegistryYear);
Upvotes: 5