EPO - Discussion forums

Hello, I am receiving several errors when setting foreign keys in Postgresql. Wondering if anyone has suggestions? The errors fall into two categories - here are examples of each with the SQL syntax that fails:

Error type 1 (2 cases)
ALTER TABLE tls226_person_orig ADD FOREIGN KEY (person_id) REFERENCES tls906_person(person_id);
ERROR: insert or update on table "tls226_person_orig" violates foreign key constraint "tls226_person_orig_person_id_fkey"
DETAIL: Key (person_id)=(52249751) is not present in table "tls906_person".
(Note: In both cases, it looks like the referenced table is missing a key.)

Error type 2 (5 cases)
ALTER TABLE tls228_docdb_fam_citn ADD FOREIGN KEY (docdb_family_id) REFERENCES tls201_appln(docdb_family_id);
ERROR: there is no unique constraint matching given keys for referenced table "tls201_appln"

I am not a database expert, so any help would be very appreciated!

Regarding your point 1:
Yes, there is a slight inconsistency, due to some earlier data cleaning. But I only see a single inconsistency, not 2 occurrences;
You can resolve this issue by removing the record with PERSON_ID = 52249751 from table TLS226_PERSON_ORIG.
The next PATSTAT version will correct this.

Regarding your point 2:
Good observation. In fact, the attributes in table TLS228_DOCDB_FAM_CITN are not Foreign Keys, because they do not refer to the Primary Key APPLN_ID of table TLS201_APPLN. The PATSTAT documentation will be updated accordingly.
In short, to fix this issue: You should not define a FK constraint between the tables TLS228 and TLS201.

Just a note: Defining Foreign Key constraints might be a lot of effort in a database which takes its data from many different sources of varying quality. The EPO spends considerable effort to make the database as consistent as possible, but likely there will always be some inconsistencies. They are too minor to affect real statistical work, but might be a hassle when working with strict Foreign Key constraints. Probably you should discuss whether FK constraints in the PATSTAT context bring more benefit than effort or not.

I hope this helps,
Martin