CODEWITHSUNDEEP
mysqlsqlkeyconstraints
Ethan Allen
Ethan Allen

Reputation: 14835

Is there a way to find out if my data is connected by a foreign key constraint or not in MySQL?

I have a table barcodes that has 1000's of rows of barcode in it. Most of the barcodes are attached to another table (there are many different tables it can be connected to) via a foreign key constraint, so if I ran the following query, it would error out because of the constraint:

delete from barcodes

But somehow (by some design flaw I created within my database) there are barcodes in the table that aren't connected by any constraint, and are just free floating.

Is there some easy query to pull back only the barcodes that aren't connected by a constraint? Something like this pseudocode:

select * from barcodes where constraint = null

Upvotes: 1

Views: 170

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

The constraint isn't on the barcodes table, it's on other tables that depend on barcodes. The constraint prevents you from deleting specific barcodes if there are rows in the other tables that depend on those specific barcodes. So you need to find out if there are any rows in dependent tables.

SELECT b.*
FROM barcodes b
LEFT OUTER JOIN child_table c ON b.barcode_id = c.barcode_id
WHERE c.barcode_id IS NULL;

Explanation: This query tries to match rows from barcodes to an hypothetical table barcode_child, and where there is no matching row in barcode_child, then the OUTER JOIN returns NULL for all the columns, meaning the row in barcode is free to be deleted.

You may have several tables that reference barcodes. The foreign key constraints will be defined in those tables. You can find out which tables have constraints depending on barcode with this query:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE (REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME) =
      ('barcode_schema', 'barcode', 'barcode_id');

I made assumptions on the barcode_schema and barcode_id names, you'll have to change those to suit your environment.

Once you find the several tables that reference barcodes, you can put together one query that tests all of them at once, and finds barcodes that are completely free of all dependents:

SELECT b.*
FROM barcodes b
LEFT OUTER JOIN child_table1 c1 ON b.barcode_id = c1.barcode_id
LEFT OUTER JOIN child_table2 c2 ON b.barcode_id = c2.barcode_id
LEFT OUTER JOIN child_table3 c3 ON b.barcode_id = c3.barcode_id
...
WHERE COALESCE(c1.barcode_id, c2.barcode_id, c3.barcode_id, ...) IS NULL;

This is just an extension of the first SELECT I showed above. Join the barcode to all of its dependent tables, and if no match is found in any of those tables, the COALESCE() of all their columns is still NULL.

Upvotes: 2

Related Questions