Re: Disabling referential integrity - Mailing list pgsql-general

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
>