3

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

M.Lopez
  • 67
  • 6

2 Answers2

5

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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)
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • Hey i follow your advise on using UNIQUE INDEX, but now i have another problem, the program that im creating does not recived any error when creating the row with the same number and date(It does not create the row either) but i need that this crate an error so the user can corrected. – M.Lopez Feb 04 '14 at 16:58