Referential Integrity on one of our production tables seems to have been
lost. I am running Postgres 7.1.3 embedded within Red Hat
kernel-2.4.9-e.49.
Within that I have a table with referential integrity constraints which
no longer work.
I do not know how to disable referential integrity on a column in a table.
I do not know how to view what Postgres thinks my referential integrity
constraints are on this table.
I do ...-c"\d table_with_referential_integrity" and here's what I get:
[~]$ mpt -c"\d pat_emp_ins"
Table "pat_emp_ins"
Attribute | Type | Modifier
---------------------------+-----------+----------
pat_id | text | not null
ins_co_id | text | not null
employer_id | text | not null
insurance_group | text |
note | text |
print_note_primary | boolean |
print_note_secondary | boolean |
Indices: pat_emp_ins_employer_id_key,
pat_emp_ins_ins_co_id_key,
pat_emp_ins_pat_id_key
[~ create_tables_for_database]$
And here is the SQL I used to generate this table:
--
create table pat_emp_ins (pat_id text not null
references patient,
ins_co_id text not
null
references insurance_company,
employer_id text not null
references employer,
insurance_group text,
note text,
print_note_primary boolean,
print_note_secondary boolean,
unique(pat_id,ins_co_id,employer_id));
--
Problem is, my users using my application are able to insert rows into
"pat_emp_ins" table which have values for "employer_id" and/or
"ins_co_id" which do not exist in the referenced tables. This seems to
have happened recently but I do not know how recently. This application
has been running production since 2003-11-07.