Enforce Data Integrity without a direct Foreign Key relationship

Question

I have a new table that will have a column named County_Codes. I would like to enforce that data to a list of County_Codes from another table named Institutions. However, due to this being a legacy, poorly designed table I do not currently have the option of re-writing the database and this is the best source of this data. The values of County_Codes in the Institutions table can appear multiple times, or be NULL. I can generate a query to create a unique list of values from this table:

select distinct County_Codes from dbo.Institutions

Is there a way to enforce records in the new table to one of those county codes in the database?

Example of County codes (they're char(5)):

00001
00101
10101
11000
90563
etc...

Answer 1

If I am understanding the situation correctly, then is there any reason why you can't extract that distinct list into a table to be a lookup table, CountyCodes, create a PK on those "codes" (the CountryCode column), and then add a Foreign Key to the new table referencing that PK of the new CountryCodes lookup table?

CREATE TABLE dbo.CountryCodes
(
  CountryCode CHAR(5) NOT NULL
                CONSTRAINT [PK_CountryCodes],
  CountryName NVARCHAR(50) NULL
);

INSERT INTO dbo.CountryCodes (CountryCode)
  SELECT DISTINCT inst.Country_Codes
  FROM   dbo.Institutions inst
  WHERE  inst.Country_Codes IS NOT NULL;

ALTER TABLE dbo.NewTable
  ADD CONSTRAINT [FK_NewTable_CountryCodes]
  FOREIGN KEY ([CountryCode])
  REFERENCES dbo.CountryCodes ([CountryCode]);

-- optional FK for existing table:
ALTER TABLE dbo.Institutions
  ADD CONSTRAINT [FK_Institutions_CountryCodes]
  FOREIGN KEY ([CountryCode])
  REFERENCES dbo.CountryCodes ([CountryCode]);

And at that point, you could then add a Foreign Key on the existing table's County_Codes column to the PK of the new lookup table, right?

I am assuming that the values are 2 or 3 letter ISO country codes, and that each row of the existing table -- Institutions -- has one code in the Country_Codes column, even though the column's name is in the plural form.

IF for some reason the suggestion above will not work, then you can always create an AFTER INSERT, UPDATE Trigger on the new table to ensure that all values in INSERTED.CountryCode are IN (SELECT DISTINCT Country_Codes FROM dbo.Institutions WHERE Country_Codes IS NOT NULL) or are NULL. But doing that lookup each time can be expensive, hence the suggestion above to extract the list one time into a table.

The other benefit of extracting the distinct list of Country_Codes into a lookup table is that the values currently in the Institutions table might not be ALL possible values of valid Country Codes, so adding a new CountryCode to the new table could fail if only checking the values in the Institutions table. By having a separate lookup table, you can add values that are valid but have not yet been used. And if they are the 2 or 3 character ISO codes, you can get the full list from the ISO website and pre-populate the lookup table.