Re: Foreign keys question (performance)

On Sun, Dec 4, 2011 at 7:41 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys <haramrae(at)gmail(dot)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't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises 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 still referencing 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