Re: Foreign keys question (performance) - Mailing list pgsql-general

On Sun, Dec 4, 2011 at 7:41 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys <haramrae@gmail.com> wrote:
>> On 4 Dec 2011, at 11:19, Phoenix Kiula wrote:
> ....
>
>>
>> INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there
can'tbe a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the
databaseraises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child
table.
>>
>> Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're
stillreferencing a valid row. 




I have a problem.

Here's my table designs. The problem is that if Table 1 (stores) has a
foreign key reference from another child table (stores_registered),
then when I update Table 1, it throws an error that referential
intergrity is being violate because Table 2 depends on Table 1.
However, if I update Table 2 first, it tells me  that the fkey in
Table 1 doesn't exist (of course).

Any ideas? What am I missing? How do updates work in terms of CASCADE?

Thanks!





mydb=# \d stores



                              Table "public.stores"
     Column      |            Type             |            Modifiers
-----------------+-----------------------------+---------------------------------
 strid           | character varying(35)       | not null
 plc             | text                        | not null
 user_registered | boolean                     |
 private_key     | character varying(6)        | default NULL::character varying
 modify_date     | timestamp without time zone | default now()
 ip              | bigint                      |
 plc_md5         | text                        |
Indexes:
    "idx_stores_pkey" PRIMARY KEY, btree (strid)
    "idx_stores_ip_plc" UNIQUE, btree (ip, plc_md5)
    "idx_stores_modify_date" btree (modify_date)
    "idx_stores_plcmd5" btree (plc_md5)
Check constraints:
    "stores_strid_check" CHECK (strid::text ~ '[-.~a-z0-9_]'::text)
Referenced by:
    TABLE "stores_registered" CONSTRAINT "fk_stores_registered"
FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE
CASCADE ON DELETE CASCADE
    TABLE "stores_stats" CONSTRAINT "fk_stats" FOREIGN KEY (strid)
REFERENCES stores(strid) ON DELETE CASCADE
    TABLE "interesting" CONSTRAINT "interesting_strid_fkey" FOREIGN
KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON
DELETE CASCADE




mydb=# \d stores_registered





    Column    |            Type             |            Modifiers
--------------+-----------------------------+---------------------------------
 strid        | character varying(35)       | not null
 plc          | text                        | not null
 user_id      | character varying(30)       | not null
 modify_date  | timestamp without time zone | default now()
 plc_md5      | text                        |
Indexes:
    "idx_stores_registered_pkey" PRIMARY KEY, btree (strid)
    "idx_stores_registered_userid_plc" UNIQUE, btree (user_id, plc_md5)
Check constraints:
    "stores_strid_check" CHECK (strid::text ~ '[-.~a-z0-9_]'::text)
    "stores_plc_check" CHECK (plc <> ''::text)
Foreign-key constraints:
    "fk_stores_registered" FOREIGN KEY (strid) REFERENCES
stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
    "stores_registered_users_fkey" FOREIGN KEY (user_id) REFERENCES
users(id) MATCH FULL ON DELETE CASCADE