1

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...
6
  • What are some example values from County_Codes from dbo.Institutions? Dec 2, 2016 at 21:54
  • @srutzky added some examples
    – DForck42
    Dec 2, 2016 at 21:58
  • Do those values have any meaning? What does 10101 translate to? Dec 2, 2016 at 22:04
  • no, I made those values up, but they're the same form of the data in the table. they'll be in the form ##### (basically, all numeric). I don't want to publicly post any real data here, hence the generic names and made up data.
    – DForck42
    Dec 2, 2016 at 22:09
  • but the real values have leading zeroes, hence can't be an INT column? Either way, that doesn't change my suggestion. It still makes sense to have a CountryName field so that a query can translate the numeric code to human-readable if / when desired. Dec 2, 2016 at 22:11

1 Answer 1

3

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.

2
  • while I like the idea of creating the new table, I'm not sure if that'll work for us. i'll see what we want to do come Monday (the dev that was asking has gone home for the evening). I am also hesitant about the trigger, but that might have to be the way we go. I greatly appreciate your input
    – DForck42
    Dec 2, 2016 at 22:11
  • Why would creating a table be a problem? Why is a Trigger a problem? I am only asking so that I can provide better feedback. At the end of the day, these are the only two ways, outside of creating a UDF that does the lookup to Institutions and returns a true/false, add that as a non-persisted computed column on the new table, and then add a CHECK Constraint on the new table that ensures that the UDF returns true / 1. But that is kinda ugly compared to the Trigger. Besides, enforcing data integrity is a primary reasons for Triggers to exist, so it is a fairly nature fit if you can't add an FK. Dec 2, 2016 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.