I'm writing an application that requires all users to access data on a central database using MySQL, and I was wondering something.
Let's say I have this setup.
CREATE TABLE A
(
id INT PRIMARY KEY AUTO_INCREMENT,
data INT NOT NULL;
);
CREATE TABLE B
(
id INT PRIMARY KEY AUTO_INCREMENT,
a_id INT,
FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE SET NULL
);
Now, the way I want this set up is, table A must ALWAYS be referenced by a row in table B. However, a row in table B may or may not reference a row in table A. The relationship is 1:n in that multiple rows in table B can reference a single row in table A. I am just wondering if it is possible to have the MySQL database automatically delete a row in A if it is no longer referenced by any row in table B.
The idea here is that I can simply set a_id in table B to NULL and have the database cleanup whatever is left. I guess that's similar to Java garbage collection now that I think about it. If there is no key to automatically enforce the constraint, would a trigger executed after an update work?
EDIT: Adding in the additional relationship constraint.
Run the following query at a specific interval:
DELETE tableA
FROM tableA LEFT JOIN tableB B ON A.id = B.a_id
WHERE B.a_id IS NULL;
Or, to maintain real-time consistency, you could create an OnChange trigger on tableB that performs similar.