Find duplicate fields in SQL

Let's say that you have a table named users, where you keep your application users. If you has forgotten to set the username field as unique and you have some duplicate fields, you can use the following query:

SELECT username, COUNT(*)
FROM users
GROUP BY username
HAVING COUNT(*) > 1

Create MySql Trigger before deleting a field

I asked some help from StackOverFlow and I had some partial help. I have a table with departments and the table user is referencing the department table, so every user belongs to one department (one to many). When I have to delete an old deparment it happens that there are some users associated to that department. Since a business rule is to have each user associated to one department or at least to department #1, which is the company it self, I want to set all users' department to 1, if the referenced department is deleted. This can be done with your programming language code or with a trigger, I decided to have a trigger (althogh I usually prefer to use the programming language and MySql as persistent layer, but this is another story).

I have used this code:

CREATE TRIGGER dept_set_user_to_wuk BEFORE DELETE ON dept
FOR EACH ROW UPDATE user SET deptid = 1 WHERE deptid = OLD.deptid;

I have created a trigger named dept_set_user_to_wuk saying that before deleting a row in dept (BEFORE DELETE ON dept), it should update each user's department to the default ID #1. It is worth to mention that the where clause is very important, since I used the OLD keyword. OLD keyword reference the field of the table specified after ON, in this case is the 'dept' table.

Find orphan rows in MySql

If you have two tables, with already date on both of them, when you try to apply a foreign key to have referential integrity, you might get this error:

Cannot add or update a child row: a foreign key constraint fails ...

This means that you have one or more rows, in the referenced table, which is missing, so you are trying to reference a row that does not exist. You can use this SQL query to find the missing ids of the referenced table:

SELECT t1.id_referencing_t2
FROM a_table AS t1
LEFT JOIN referenced_table AS t2
ON t1.id_referencing_t2 = t2.primary_id_of_t2
WHERE t2.primary_id_of_t2 IS NULL

You have to substitute the following name with your real one:

id_referencing_t2: the id the reference the primary key on the referenced table.
primary_id_of_t2: the primary key of the referenced table.
a_table: the table that references the referenced table.
referenced_table: is the table that you want to reference, the table that have the missing ids.

The result of the query is the primary key of the rows that points to missing id in the other table. With those ids you can alter the value to a default one, or delete the rows.

Now you should be able to apply referencial integrity to your tables.