Here are the details on how to fix it should anyone stumble upon the same issue.
When you delete an entry from
eav_attribute_set it will also delete foreign-key related entries from consequent tables based on
entity_type_id which is set as a foreign-key for this matter.
This deletion happens because Magento uses
ON DELETE CASCADE rule which tells the Sql engine to also delete all FK related entries from other tables.
In my case, the deleted entry was the Default for categories, which is
entity_type_id:3. So here are the steps towards the fix:
1) use this query to find out what tables are related on delete cascade rule
WHERE constraint_schema = 'your_db_name' AND
referenced_table_name = 'eav_attribute_set' AND
delete_rule = 'CASCADE';
This resulted in 3 other tables:
2) the only table that might have been affected - in my case - was
'eav_attribute_group' since the products use entity_type=4 and that was not deleted.
3) to verify if this is the culprit, use the below query to find any values with
SELECT * FROM your_db_name.eav_attribute_group where entity_type_id=3;
4) results: 0 - that's bad I know
5) now you need to verify if this table also uses ON DELETE CASCADE rule, and something else might got deleted without you knowing.
So use the above query in step 1 but replace the table_name with eav_attribute_group.
This resulted in one other table:
6) run this query similar to step 3 to verify if any values exist with
attribute_set_id=3, again replace the table to eav_entity_attribute.
SELECT * FROM your_db_name.eav_entity_attribute where attribute_set_id=3
7) results: 0 - scary!
8) what you see here before your eyes is, that values from two other tables were deleted with a delete of a single record from just one table
You'll definitely want to add back those values exactly the way they were before.
Use a backup copy if you have one, or use a dev/staging/production environement to mimic the values accordingly.
In my case we have a staging site set up so it was pretty easy to identify all 37 missing values for
eav_entity_type=3 in both tables and everything suddenly got healed.
Of course don't forget to add back the originally deleted record in table