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_refe rential_integri ty" and here's what I get:
[~]$ mpt -c"\d pat_emp_ins"
Table "pat_emp_in s"
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_prim ary | boolean |
print_note_seco ndary | boolean |
Indices: pat_emp_ins_emp loyer_id_key,
pat_emp_ins_ins _co_id_key,
pat_emp_ins_pat _id_key
[~ create_tables_f or_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_compa ny,
employer_id text not null
references employer,
insurance_group text,
note text,
print_note_prim ary boolean,
print_note_seco ndary boolean,
unique(pat_id,i ns_co_id,employ er_id));
--
Problem is, my users using my application are able to insert rows into
"pat_emp_in s" table which have values for "employer_i d" 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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org