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 !!!
The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE on the master table will propagate and delete matching rows from all derived tables.
You will have to use InnoDB tables for that.
I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which case you may try using triggers, see this post for an example. Triggers are bad for performance, mind you.
Those who do not define foreign keys tend to work out all DELETEs in application code.
Best Answer
Reading your question literally, "if we delete 1 of 2 records pointing to Table2 from Table1 would also delete referenced record from Table2", you just need an ordinary
ON DELETE CASCADE, as you've shown: Table2 references Table1, and deleting a record in Table1 causes all related records in Table2 to be deleted, even if those records are being pointed to by other records in Table1.However, if your goal is instead to delete from Table2 when there are no more associated records in Table1, whereas there are normally two such records, you'll need something more elaborate.
I think better in concrete terms, so based on your pre-edit question, I'll call Table1
Guardians(because "parents" can be confusing for SQL-heads when they're actually the child half of a relationship) and Table2Students(similarly, avoiding the word "children"). The goal is to ensure that any students that do not have any guardians get deleted.If polygamy and divorce are unheard on, one option would be to have two NULLable fields in
Students,FatherIDandMotherID(orParent1andParent2, to allow for step-parents, gay marriage, etc.). Set these to referenceGuardianswithON DELETE SET NULL, and add anON UPDATEtrigger toStudentsto delete a record if both fields become NULL. If Bob's father is deleted but he still has a mother, he's OK, but if his mother is deleted the poor orphan gets wiped from the database. This assumes that aCASCADE UPDATEevent triggers triggers, I don't know that for a fact.A more robust solution would be to create a third table relating
GuardianstoStudents. It would include fieldsGuardianID,StudentID,EffectiveDate(these three making up a candidate key),StopDate, and maybe fields to indicate the rights of the guardian; perhaps only one has legal custody, and the other should not be allowed to remove the student from school grounds. This would allow for death or divorce: just set the old record'sStopDate. Insert a new record if the remaining guardian ever remarries. You could create anON UPDATEtrigger and anON DELETEtrigger on this third table, checking to see if there are now any students which lack active guardians.However, before putting too much engineering into this, consider: do you really need to cascade? Keep the referential integrity, of course, but just enforce deletion logic in your business logic layer. For that matter, does it even make sense to delete students? If you're asked how many students took a specific class in 2011, but you've deleted some because they've subsequently dis-enrolled, you'll have an undercount.