Creating a trigger is successful
mysqli_query($this->conn,
"CREATE TRIGGER `before_delete_address`
BEFORE DELETE ON address FOR EACH ROW
DELETE FROM city WHERE cityid = OLD.cityid");
but when I execute the following query
DELETE FROM address WHERE addressid = 18
it outputs the following error code
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`gamecomp`.`address`, CONSTRAINT `cityid` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`) ON UPDATE CASCADE) 0.039 sec
The constraints on the address table are set to On Update cascade and On Delete restrict.
The columns of the address table are addressid, address, cityid, zipcode, cityid is a FK reference to the city table contains cityid, city, countryid
Can someone point out what I have done wrong? thanks
instead of BEFORE DELETE ON city use AFTER DELETE ON address
address FOREIGN KEY (cityid) REFERENCES city (cityid) ON DELETE RESTRICT means that you may not delete a city that any address.cityid references. Since that is exactly what your trigger does, you get a foreign key constraint error.
Even if you make this trigger an AFTER DELETE ON, you might still have other address rows that reference a city, which means you might still get a foreign key constraint error. You should also verify that there are no triggers on the city table which may be upsetting constraints on foreign keys in address.
You need to verify first that all references to the cityid are gone before you delete the corresponding city. Frankly I'm not convinced you should even be trying to do this. In a well-designed schema, some orphaned records should not be a problem. If they really trouble you they can be deleted with a cronjob periodically instead of with a trigger.
If you insist on a trigger, try this:
CREATE TRIGGER `delete_city_orphans`
AFTER DELETE ON address FOR EACH ROW
DELETE FROM city WHERE city.id=OLD.cityid
AND NOT EXISTS (SELECT * FROM address WHERE cityid=OLD.cityid)