LOST REFERENTIAL INTEGRITY

From: "Jimmie H(dot) Apsey" <japsey(at)futuredental(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: "Apsey, Jim (at) DCF" <japsey(at)futuredental(dot)com>
Subject: LOST REFERENTIAL INTEGRITY
Date: 2004-10-04 17:46:18
Message-ID: 41618C6A.3030604@futuredental.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Taber, Mark 2004-10-04 18:28:33 PostgreSQL 8.0 install woes
Previous Message Gaetano Mendola 2004-10-04 17:34:52 Re: VACUUM FULL on 24/7 server