3

SQL Server 2016 has a view sys.time_zone_info. In my table I store the Time Zone Name (from that view). Now, I cannot (apparently) simply define a FK from my table's column to that view:

ALTER TABLE [dbo].[MyTable] 
   ADD CONSTRAINT [TimeZone_Name_FK] 
   FOREIGN KEY ([TimeZone_Name]) REFERENCES sys.time_zone_info([Name])

I would like to ensure that the data in my table is validated against that view. How would you suggest I handle this? A trigger seems to be overkill.

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Frank Monroe
  • 1,557
  • 2
  • 13
  • 20
  • 1
    http://stackoverflow.com/questions/447122/can-i-have-a-foreign-key-referencing-a-column-in-a-view-in-sql-server – artm Apr 20 '17 at 03:10
  • In order for a foreign key to work, the column you reference in the *parent table* must be either the **primary key** of that table, or it has to be part of a **unique constraint** on that table. Is either of this the case with `Name` in `sys.time_zone_info` ?? – marc_s Apr 20 '17 at 04:36
  • Possible duplicate of [TSQL foreign keys on views?](http://stackoverflow.com/questions/1928355/tsql-foreign-keys-on-views) – Hybris95 Apr 20 '17 at 09:14
  • Here is what I did. This is not perfect as there is a performance hit, but there are not that many lines in the table. ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT CHECK ([dbo].[Is_TimeZone_FN]([TimeZone_Name]) <> 0) ... using a simple function that checks the TimeZone. Is there a better way? Any comments? ... – Frank Monroe Apr 21 '17 at 16:04

0 Answers0