Thread: Disabling referential integrity
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.
Ok I tried that, still no luck, i set tgdeferrable to 't' for all the constraints as you can see below. I then put the
deletestatement within a transaction that set all constraints to deferred, but still get a referential integrity
violation.
I ran this statement:
taupo=# BEGIN;
BEGIN
taupo=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
taupo=# delete from faps_key where substring(key_code from 1 for 1) = 'B';
DELETE 400
taupo=# COMMIT;
ERROR: archivefapskey_keycode_fk referential integrity violation - key in faps_key still referenced from
archive_faps_key
taupo=#
Still have all 400 entries.
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 | t | 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 | t | 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 | t | 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 | t | 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 | t | 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 | t | f | 6 | |
faps_key_permitid_fk\000faps_key\000faps_permit\000UNSPECIFIED\000permit_id\000permit_id\000
(7 rows)
> -----Original Message-----
> From: Ian Harding [mailto:iharding@tpchd.org]
> Sent: Friday, 17 October 2003 20:16
> To: Sharon Cowling
> Subject: Re: [GENERAL] Disabling referential integrity
>
>
> Try making the constraint deferrable. That puts off
> checking until the
> end of the transaction.
>
> Sharon Cowling wrote:
>
> >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/hackingreferentialint
egrity.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.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>------------------------------------------------------------------------
>
>SPAM: MIME_QP_LONG_LINE (0.2 points) Quoted-printable line longer than 76 chars
>SPAM: BAYES_70 (2.3 points) Bayesian classifier says spam probability is 70 to 80%
>SPAM: FORGED_MUA_OUTLOOK (3.0 points) Forged mail pretending to be from MS Outlook
>Score Total: 5.5
>
On Sun, 19 Oct 2003, Sharon Cowling wrote: > Ok I tried that, still no luck, i set tgdeferrable to 't' for all the > constraints as you can see below. I then put the delete statement > within a transaction that set all constraints to deferred, but still get > a referential integrity violation. > > I ran this statement: > taupo=# BEGIN; > BEGIN > taupo=# SET CONSTRAINTS ALL DEFERRED; > SET CONSTRAINTS > taupo=# delete from faps_key where substring(key_code from 1 for 1) = 'B'; > DELETE 400 > taupo=# COMMIT; > ERROR: archivefapskey_keycode_fk referential integrity violation - key > in faps_key still referenced from archive_faps_key > taupo=# > > Still have all 400 entries. You'd still need to insert the appropriate entries before the transaction commits with deferral. It just means that the delete works so that you can insert the new rows before committing, but at commit time the constraint must be satisfied. If you really want to not have the constraint fire until some later point and you can't put everything in a single transaction, you're probably best off with dropping the constraint and re-adding it. It'll be slow, but it should guarantee that you didn't orphan any rows. If you're willing to risk corruption and are a superuser, you can temporarily turn off triggers for the table in question by setting reltriggers to 0 for the pg_class row and then setting it back to the correct value when you're done. This won't work if you have other triggers/fk constraints that you want to have run, though.