Find orphan rows in MySql
If you have two tables, with already date on both of them, when you try to apply a foreign key to have referential integrity, you might get this error:
Cannot add or update a child row: a foreign key constraint fails ...
This means that you have one or more rows, in the referenced table, which is missing, so you are trying to reference a row that does not exist. You can use this SQL query to find the missing ids of the referenced table:
SELECT t1.id_referencing_t2
FROM a_table AS t1
LEFT JOIN referenced_table AS t2
ON t1.id_referencing_t2 = t2.primary_id_of_t2
WHERE t2.primary_id_of_t2 IS NULL
You have to substitute the following name with your real one:
id_referencing_t2: the id the reference the primary key on the referenced table.
primary_id_of_t2: the primary key of the referenced table.
a_table: the table that references the referenced table.
referenced_table: is the table that you want to reference, the table that have the missing ids.
The result of the query is the primary key of the rows that points to missing id in the other table. With those ids you can alter the value to a default one, or delete the rows.
Now you should be able to apply referencial integrity to your tables.
posted by Riccardo Tacconi @ 04:32
3 Comments:
This comment was a huge help.
I made some improvements on the idea, mainly because I had multi-language string lookup table with constraints in 11 other tables with 23 FKs to left join.
Below is a set of mySql statements which will automatically find and display all orphaned rows (after displaying them all, I changed stmt1 to a delete statement to get rid of them)
use ;
#ensure there is enough room for group_concat (you may need to adjust this)
set @@group_concat_max_len = 4096;
#determine which foreign keys are associated with the table expected to have orphans
drop table if exists fk_lookup;
create temporary table fk_lookup
select
CONCAT( REFERENCED_TABLE_SCHEMA, '.', REFERENCED_TABLE_NAME ) as orphan_table
, CONCAT( REFERENCED_TABLE_SCHEMA, '.', REFERENCED_TABLE_NAME, '.', REFERENCED_COLUMN_NAME) as orphan_column
, CONCAT( TABLE_SCHEMA,'.',TABLE_NAME) as fk_table
, CONCAT( TABLE_SCHEMA,'.',TABLE_NAME,'.',COLUMN_NAME) as fk_column
from
information_schema.KEY_COLUMN_USAGE
where
REFERENCED_TABLE_SCHEMA = ''
and
REFERENCED_TABLE_NAME = '';
#create the strings for joining the tables
drop table if exists orphan_query;
create temporary table orphan_query
( fk_query int
, orphan_table text
, fk_join text
, fk_where text )
select
1 as fk_query
, orphan_table
, CONCAT( fk_table
, ' on '
, GROUP_CONCAT(orphan_column, ' = ', fk_column separator ' or ')
) as fk_join
, GROUP_CONCAT('ISNULL(', fk_column, ')' separator ' and '
) as fk_where
from fk_lookup
group by fk_table;
#create the final statement
set @fk_stmt = ( select CONCAT(
'select ',orphan_table,'.* from ',orphan_table, ' left join '
, GROUP_CONCAT( fk_join separator ' left join ' )
, ' where '
, GROUP_CONCAT( fk_where separator ' and ' )
) as foo
from orphan_query
group by fk_query );
#run the query to show all orphans in the given table
prepare stmt1 from @fk_stmt;
execute stmt1;
Thanks,
clobber
Thanks, this worked for me:
SELECT t1.uid
FROM tx_pbsurvey_results AS t1
LEFT JOIN tx_pbsurvey_answers AS t2
ON t1.uid = t2.result
or even
DELETE t1
FROM table1 AS t1
LEFT JOIN t2 AS t2
ON t1.uid = t2.result
WHERE t2.result IS NULL
Wow to both the post AND the first person who commented! They're both about to be super helpful to me for my exact situation. :)
Post a Comment
Subscribe to Post Comments [Atom]
<< Home