Question 231440 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL, check if foreign key assigned to any table

I have table files
Also, I have more that 100 tables where I use relation/foreign key as file_id

How can I get all files with non-existing relations (it means that file not attached to any table)?

Tags:

2

Answers


  1. 0

    As I understand your question, you are looking for all entries in table files where there is no entry in any of the other tables (xyz1, xyz2, xyz3) referencing that said file. In the following example, your want files 2 and 4 as a result, right?

    files

    id
    1
    2
    3
    4
    5

    xyz1

    id file_id
    1 1

    xyz2

    id file_id
    1 3

    xyz3

    id file_id
    1 5

    The not so intelligent approach. Build up an ugly statement like this:

    SELECT * FROM files f WHERE

NOT EXISTS (SELECT file_id FROM xyz1 WHERE f.id = xyz1.file_id)
AND NOT EXISTS (SELECT file_id FROM xyz2 WHERE f.id = xyz2.file_id)
AND NOT EXISTS (SELECT file_id FROM xyz3 WHERE f.id = xyz3.file_id)
;

    To easily get the 100 AND NOT EXISTS lines, get all table names having a field named file_id and concatenate them into SQL lines (replace your_database_name with your actual database name):

    SELECT CONCAT('AND NOT EXISTS (SELECT file_id FROM ', TABLE_NAME, ' WHERE f.id = ', TABLE_NAME, '.file_id)')
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'your_database_name'
AND COLUMN_NAME = 'file_id';

    Copy the query result, add the lines to the first SQL query above, run it and you should have the desired files.

    Login or Signup to reply.
  2. 0

    I am usually do like this in bash(maybe possible to make SQL, but I am not sure which table)

    table_names=`echo "show tables"|mysql database_name -q -s|grep -v "show tables"`
for table in $table_names
do
  echo $table
  mysqldump database_name $table |grep file_id
done;

    And you will see anything related to file_id in each one table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search