From: | Sharon Cowling <sharon(dot)cowling(at)sustema(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Disabling referential integrity |
Date: | 2003-10-17 04:08:00 |
Message-ID: | 200310170415.h9H4FtA03765@lambton.sslnz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi, my problem is this:
I have a table called faps_key the unique identifier being key_code:
taupo=# \d faps_key
Table "faps_key"
Column | Type | Modifiers
-----------------+-----------------------+-----------
key_code | character varying(6) | not null
date_key_issued | date |
date_key_due | date |
key_issued_by | character varying(12) |
description | character varying(20) |
comments | character varying(30) |
permit_id | integer |
status | character varying(10) |
Primary key: faps_key_key_code_pk
Triggers: RI_ConstraintTrigger_243657,
RI_ConstraintTrigger_243665,
RI_ConstraintTrigger_243667
Problem is the users now want the old key_codes deleted, and new ones entered, some of which are the same as the old ones. So I need to disable the referential integrity constraints in order to delete the old codes and input the new ones. I've had a look through the archives and at the below link, however i'm having trouble.
Associated tables are:
\d faps_permit
Table "faps_permit"
Column | Type | Modifiers
-------------------+------------------------+-----------
permit_id | integer | not null
person_id | integer | not null
date_from | date | not null
date_to | date | not null
location | character varying(30) | not null
purpose | character varying(30) | not null
subpurpose | character varying(30) | not null
vehicle_rego | character varying(6) |
vehicle_type | character varying(30) |
dogs | character varying(3) |
permit_conditions | character varying(300) |
other_info | character varying(300) |
issued_by | character varying(12) | not null
issue_date | date |
permit_printed | integer |
firearms_licence | character varying(20) |
drivers_licence | character varying(10) |
cancel_permit | character varying(10) |
Primary key: faps_permit_permit_id_pk
Triggers: RI_ConstraintTrigger_243659,
RI_ConstraintTrigger_243661
\d archive_faps_key
Table "archive_faps_key"
Column | Type | Modifiers
-----------------+-----------------------+-----------
key_code | character varying(6) | not null
permit_id | integer | not null
date_key_issued | date |
date_returned | date |
date_key_due | date |
status | character varying(10) |
key_issued_by | character varying(12) |
Primary key: archivefapskey_keycode_permit_i
Triggers: RI_ConstraintTrigger_243663
I checked out the following link, and followed the instructions
http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php
but i still can't delete rows.
As you can see from below all tgenabled fields are set to 'f'. but further below when I try to delete it still fails.
taupo=> select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs
---------+-----------------------------+--------+--------+-----------+----------------+---------------------------+---------------+--------------+----------------+---------+--------+------------------------------------------------------------------------------------------------------
1260 | pg_sync_pg_pwd | 1689 | 29 | t | f | | 0 | f | f | 0 | |
233004 | RI_ConstraintTrigger_243663 | 1644 | 21 | f | t | archivefapskey_keycode_fk | 233001 | f | f | 6 | | archivefapskey_keycode_fk\000archive_faps_key\000faps_key\000UNSPECIFIED\000key_code\000key_code\000
233001 | RI_ConstraintTrigger_243657 | 1644 | 21 | f | t | faps_key_permitid_fk | 232998 | f | f | 6 | | faps_key_permitid_fk\000faps_key\000faps_permit\000UNSPECIFIED\000permit_id\000permit_id\000
233001 | RI_ConstraintTrigger_243665 | 1654 | 9 | f | t | archivefapskey_keycode_fk | 233004 | f | f | 6 | | archivefapskey_keycode_fk\000archive_faps_key\000faps_key\000UNSPECIFIED\000key_code\000key_code\000
233001 | RI_ConstraintTrigger_243667 | 1655 | 17 | f | t | archivefapskey_keycode_fk | 233004 | f | f | 6 | | archivefapskey_keycode_fk\000archive_faps_key\000faps_key\000UNSPECIFIED\000key_code\000key_code\000
232998 | RI_ConstraintTrigger_243659 | 1654 | 9 | f | t | faps_key_permitid_fk | 233001 | f | f | 6 | | faps_key_permitid_fk\000faps_key\000faps_permit\000UNSPECIFIED\000permit_id\000permit_id\000
232998 | RI_ConstraintTrigger_243661 | 1655 | 17 | f | t | faps_key_permitid_fk | 233001 | f | f | 6 | | faps_key_permitid_fk\000faps_key\000faps_permit\000UNSPECIFIED\000permit_id\000permit_id\000
(7 rows)
taupo=> delete from faps_key where substring(key_code from 1 for 1) = 'B';
ERROR: archivefapskey_keycode_fk referential integrity violation - key in faps_key still referenced from archive_faps_key
Any ideas about how best to go about solving my problem?
Best Regards,
Sharon.
From | Date | Subject | |
---|---|---|---|
Next Message | Nagib Abi Fadel | 2003-10-17 08:04:48 | XOR logical operator |
Previous Message | Stephan Szabo | 2003-10-17 03:36:00 | Re: pg_atoi |