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);
or
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);
or
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 !!!
You can't create a foreign key that targets a relation without a unique constraint in PostgreSQL. I'm somewhat astonished to hear that you can in MySQL.
It's really against the principle of a "foreign key" - if you can have multiple values, then the target is not in fact a key at all, it's just another data column.
That said, PostgreSQL isn't just preventing you from doing this for fun. AFAIK its foreign key implementation requires on unique b-tree indexes and cannot function properly without them. Unique indexes have some important properties, in particular regarding locking and transactional updates.
In theory I guess Pg could let you use any index, but it'd then have to deal with the mess of what happens when two concurrent transactions each delete one of two key rows. Each commit, individually, leaves the key relationship intact, but if both commit, one would have to trigger a cascade delete (which must happen before commit) or an error rollback. So PostgreSQL would have to serialize the transactions, forcing one to happen after the other, which would mean taking locks on all other rows with the same value for the key, irrespective of whether they're otherwise targeted by the query. Even then it wouldn't fully do the job because a concurrent insert could re-validate the key; Pg would really need to take predicate locks.
Implementation detail aside, what does a foreign key that targets a non-unique column actually gain you? Are you trying to model a non-optional m:n relationship?
If I had to do this, I'd do it with my own triggers, but I'd do it very carefully, as it's really hard to get it right.
Best Answer
I think you must be misunderstanding
CASCADE
foreign keys, and/or the rules around foreign keys in general.A foreign key can only refer to a table with a unique constraint or primary key. So in your example, you cannot create a foreign key referencing
table_b
if there can be multiple instances of a value in the referenced column.When you
DELETE FROM ...
a table, and another table has anON DELETE CASCADE
foreign key reference to it, all referencing rows are deleted from the other table.The column referenced cannot have multiple instances of the value, because you cannot create a foreign key to a column that is not
unique
.The referring column of the table with the constraint may have multiple values that refer to the column in the referenced table. If so, when the referenced value is removed, all these are deleted, as you would expect in a
CASCADE
.Can't create the foreign key, no unique constraint or PK:
Can't add a PK or unique constraint, has duplicates: