Mysql – In thesql, find all instances of a named column that are not foreign keys

constraintforeign keyinformation-schemaMySQL

This one has had me trumped for a while!

I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys.

Now, let me back off a little and explain my real need. I have a rather large database dealing with company information. Now, I want to be able to delete (or update) a customer record, and have all related foreign keys affected by CASCADE. Now, if the foreign key constraints are not duly set everywhere, then a few records might be orphaned.

So, I want to first query to get a bird's eye view of my column. Now let's assume that all instances of this column have the same name "mycol".

I've taken note of the table REFERENTIAL_CONSTRAINTS, but got intimidated ;). One immediate constraint (pardon the pun) I've seen is: the field CONSTRAINT_NAME is populated with names like customer_ibfk_1, since the naming of FK constraints on this project was left for mysql to handle as default. This table has no field for "REFERENCED_COLUMN", and so I'm stumped.

Any help please!

Best Answer

Here's what I eventually came up with. Of course, I'm open to input/refinements from the DBA grandfathers around here! But for now, this approach is giving me the info I need.

USE INFORMATION_SCHEMA;

Get all tables that have at least one foreign key referencing a column in "mytable"

SELECT TABLE_NAME, COUNT(*) AS reference_count 
FROM REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = 'mydb' 
    AND REFERENCED_TABLE_NAME = 'mytable' GROUP BY TABLE_NAME;

Get all tables that have at least one foreign key on a column named "mycolumn"

SELECT TABLE_NAME 
FROM KEY_COLUMN_USAGE 
WHERE CONSTRAINT_SCHEMA = 'mydb' 
    AND COLUMN_NAME = 'mycolumn' 
    AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

Get all tables that have ANY COLUMN named "mycolumn". The subquery in the WHERE clause AND (SELECT Table_Type FROM TABLES WHERE table_schema = 'mydb' AND TABLE_NAME = COLUMNS.TABLE_NAME) is necessary, otherwise it would return also any VIEW having a column named "mycolumn"

SELECT TABLE_NAME 
FROM COLUMNS 
WHERE 
    TABLE_SCHEMA = 'mydb' 
    AND COLUMN_NAME = 'mycolumn' 
    AND (SELECT Table_Type 
        FROM TABLES WHERE table_schema = 'mydb' 
        AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE';

Now, using the 2 previous queries, we can obtain any tables having a column named "mycolumn" but in which "mycolumn" is NOT A FOREIGN KEY. Note that this filtering will not respect a PRIMARY KEY, and will also return any table where "mycolumn" is a PRIMARY KEY.

SELECT TABLE_NAME 
FROM COLUMNS 
WHERE TABLE_SCHEMA = 'mydb' 
    AND COLUMN_NAME = 'mycolumn' 
    AND (SELECT Table_Type 
        FROM TABLES 
        WHERE table_schema = 'mydb' 
            AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE' 
    AND TABLE_NAME NOT IN
        (SELECT TABLE_NAME 
        FROM KEY_COLUMN_USAGE 
        WHERE CONSTRAINT_SCHEMA = 'mydb' 
            AND COLUMN_NAME = 'mycolumn' 
            AND REFERENCED_TABLE_SCHEMA IS NOT NULL
        );

WARNING !!!

Before you do anything, mysqldump the entire database or tarball /var/lib/mysql

If you are concerned about case sensitivity, you need to hunt down the usernames in a special way

I tried this little experiment

use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');

I loaded the sample data

mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE rolando
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO rolando (name) VALUES
    -> ('rolando'),('Rolando'),
    -> ('ROLANDO'),('ROLANDO'),
    -> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

I ran these queries (please note subtle differences)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name    | MD5(name)                        | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 |        2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f |        1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 |        3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name    | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando |                          1 |
| Rolando |                          0 |
| ROLANDO |                          0 |
| ROLANDO |                          0 |
| rolando |                          1 |
| rolando |                          1 |
+---------+----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,(name LIKE lower(name)) like1,
    -> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name    | like1 | like2 |
+---------+-------+-------+
| rolando |     1 |     1 |
| Rolando |     1 |     0 |
| ROLANDO |     1 |     0 |
| ROLANDO |     1 |     0 |
| rolando |     1 |     1 |
| rolando |     1 |     1 |
+---------+-------+-------+
6 rows in set (0.00 sec)

mysql>

Give this view of my experiment, a username with all uppercase or mixed case can be equal to an all lowercase UNLESS YOU USE BINARY operator with the comparison

I do not think BINARY can be applied to ON DELETE CASCADE.

However, you could something like this: To delete all usernames that are lowercase only, try running one of the following:

DELETE FROM logins WHERE username = BINARY LOWER(username);

DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

If you run this now, you might nix all usernames in the other tables. Run this instead

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

You will have to go to all tables that have a username in it and execute

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';

Since you have a ton of other tables, use the INFORMATION_SCHEMA to script it for you:

DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}

If the file is correct to you, run it in the mysql client like this

mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}

Give it a Try !!!

Naming foreign key columns

The name "creatorUser" fails when there is a multi-column foreign key relationship.

As with the other posters here, I strongly support using the same column name for the same attribute, regardless of what table it is in.

Imagine a table of books, called "Books". If the primary key is "ISBN" then the foreign key in referencing tables should be "ISBN" - not "BookID" or "BOOKISBNID" or "BookISBN".

My reference for questions like these is Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems).

Best Answer

Related Solutions

MySQL – automatically update all references to specific table to use on delete cascade

WARNING !!!

Before you do anything, mysqldump the entire database or tarball /var/lib/mysql

Naming foreign key columns

Related Question