Apart from setting @@FOREIGN_KEY_CHECKS to zero, is there any other
setting or circumstance where MySQL will allow to insert invalid IDs in
columns subject to foreigh key restrictions?

I'm trying to diagnose why I'm finding a large number of rows that point
to non-existing parent records in the database of a PHP driven app.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

In article <jcn44l$6nn$1 (AT) dont-email (DOT) me>,
Álvaro G. Vicario <alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:
What type (engine) are the tables? AFAIK, only InnoDB will enforce
foreign key constraints.

Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

El 19/12/2011 11:42, Tony Mountifield escribió/wrote:
Sorry for the omission. I should have mentioned that foreign keys are
working properly (you can't insert values that don't exist in parent
table and you get cascaded updates and deletions where set). That's why
I'm scratching my head when I see invalid data now and then.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

In article <jcn59k$b30$1 (AT) dont-email (DOT) me>,
Álvaro G. Vicario <alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:
Hmm, ok. You probably know all this anyway, but I factors I would
investigate include:

- Do the erroneous records occur in batches or individually at random?

- Are they the result of inserting a child row referring to a non-existent
parent, or of deleting a parent row with it failing to cascade?

- I would set up a cron job to scan regularly for such records:
SELECT c.id,... FROM childtable c LEFT JOIN parenttable p ON p.id=c.pid
WHERE p.id IS NULL;
and notice when the returned set changes.

- It would also be an idea to set the option in my.cnf to enable logging
of all SQL queries. This could then be correlated with a change in
output of the above query.

Hope this helps!
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?= <alvaro.NOSPAMTHANX (AT) demogracia (DOT) com.invalid> wrote:

None I'm aware of. Could the orphan records have existed *before*
the FK constraint was added? Adding an FK does not check existing
data. Neither does re-enabling @@FOREIGN_KEY_CHECKS


XL

In article <fuk3s8-ir.ln1 (AT) xl (DOT) homelinux.org>,
Axel Schwenke <axel.schwenke (AT) gmx (DOT) de> wrote:
Are you sure about that? I am sure that I have failed to add foreign
key constraints due to unsatisfied references, and needed to fix the
data by hand before it would succeed.

mysql> create table parent (id int primary key, data varchar(20)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into parent values(1,'Parent 1');
Query OK, 1 row affected (0.00 sec)

mysql> create table child (id int primary key, pid int, data varchar(20)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into child values (1,2,'Child 1 of parent 2');
Query OK, 1 row affected (0.00 sec)

mysql> alter table child add foreign key (pid) references parent(id);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql>


That I can believe.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

tony (AT) mountifield (DOT) org (Tony Mountifield) wrote:
Oops. My bad. Dunno why I was thinking that. Indeed ALTER TABLE
will check the contraints for existing rows as they are copied to
the new table.


XL