Thread: Foreign keys question (performance)

Foreign keys question (performance)

From
Phoenix Kiula
Date:
Hi.

I have a foreign key as such:


ALTER TABLE child_table
ADD CONSTRAINT fk_child
FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL
ON DELETE CASCADE ;


Questions:

1. Is "MATCH FULL" adding any value here? If the foreign key is just
on an "id" column, what purpose does it serve? Without it, the results
would be the same? Does it affect performance or should I leave it be?
(Note that the id is a alphanumeric value)

2. More importantly, in this case basically the child_table cannot
have any keys that the parent_table doesn't have either. Will INSERTs
and UPDATEs to the parent_table be slower? Or will the foreign key
check happen only when INSERT or UPDATE happen to the child_table?


Thanks!

Re: Foreign keys question (performance)

From
Alban Hertroys
Date:
On 4 Dec 2011, at 11:19, Phoenix Kiula wrote:

> Hi.
>
> I have a foreign key as such:
>
>
> ALTER TABLE child_table
> ADD CONSTRAINT fk_child
> FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL
> ON DELETE CASCADE ;
>
>
> Questions:
>
> 1. Is "MATCH FULL" adding any value here? If the foreign key is just
> on an "id" column, what purpose does it serve? Without it, the results
> would be the same? Does it affect performance or should I leave it be?
> (Note that the id is a alphanumeric value)

Nope, it is not. As I understand it, it only does something on multi-column foreign keys where parts of the key are
NULL.To quote the documentation: 

"There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will
notallow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE
allowssome foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet
implemented."

I can't say much on the impact on performance, but I'd expect that to be negligible in this case: With the MATCH FULL
inplace, it will need to check whether any of your columns are NULL, but that's only a single column in your case. 

> 2. More importantly, in this case basically the child_table cannot
> have any keys that the parent_table doesn't have either. Will INSERTs
> and UPDATEs to the parent_table be slower? Or will the foreign key
> check happen only when INSERT or UPDATE happen to the child_table?


INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't
bea reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database
raisesa 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
referencinga valid row. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


Re: Foreign keys question (performance)

From
Phoenix Kiula
Date:
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 still
referencinga valid row. 



Thanks Albert. Very useful.

I had ON DELETE...ALSO DELETE rules earlier and in some cases they let
some keys go by in associated tables. Hope foreign key constraint is
more reliable!

PK

Re: Foreign keys question (performance)

From
Phoenix Kiula
Date:
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

Re: Foreign keys question (performance)

From
"David Johnston"
Date:
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Phoenix Kiula
Sent: Tuesday, December 06, 2011 11:46 AM
To: Alban Hertroys
Cc: PG-General Mailing List
Subject: Re: [GENERAL] Foreign keys question (performance)

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'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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

------------------------------------------------------------------

If I am reading this right your issue is not "stores_registered" but
"stores_stats" - the later is missing the "ON UPDATE CASCADE" modifier to
its foreign key.

With "ON UPDATE CASCADE" when you change the primary key all related foreign
keys have their values changed as well.  With this enabled you do not need
to directly modify "table2" but instead you let the system do its thing when
you update "table1".  I believe you have the logic figured out but in this
case (and maybe the error message is simply unclear - you never did provide
your UPDATE statement nor your error message) I think it is the missing ON
UPDATE CASCADE on "stores_stats" that is your issue.

David J.




Re: Foreign keys question (performance)

From
Adrian Klaver
Date:
On 12/06/2011 08:45 AM, Phoenix Kiula wrote:
> 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:
>
>
> 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?

What is the actual error message?

>
> Thanks!
>


--
Adrian Klaver
adrian.klaver@gmail.com