There are some checks when inserting or changing records. The problem is that thousans of records have an inexistant (or no longer existant) code. To clean this up will be a lot of work.
What I could do is to set them to null and keep an additional table for reference if someone has time to look at it. Disadvantage is that if the half-wrong information is deleted is will be difficult for contributors to know that some information exists. Sometimes it probably just needs an update as the fips code may have changed in recent years and the dependent topony was not modified accordingly.
Joined: 26/04/2007 18:25:11
Messages: 40
Location: UK
Offline
Hi Marc,
For me, it would be better to add them to the admin1_codes.txt file with an appropriate name such as (obsolete) or similar. This would keep referential integrity. A separate list would work too, but I would have to import it as well.
I wouldn't NULL them - the bad information is more useful than no information.
My problem is that one of my downstream systems rejects geoname records with admin1_codes that are not in the admin1_codes table.
I also think we should be trying to fix the wrong records and having them explicit somewhere will help us know about the problem.