Magento2 Product Page 404 Error Caused by URL Rewrite Not Working As it Should

I've worked out how to fix this for our particular situation. It was a corrupt database table issue. So error from system.log file relevant is below:

main.CRITICAL: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (gamefish_mag2.report_event, CONSTRAINT REPORT_EVENT_EVENT_TYPE_ID_REPORT_EVENT_TYPES_EVENT_TYPE_ID FOREIGN KEY (event_type_id) REFERENCES report_event_types (event_type_id) ON DELE) in /var/www/vhosts/domain.com/httpdocs/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228

Restoring the report_event table to a previous version before the migration of product content from Squarespace from a month or so back did the trick. This product the product URL back to life and allowed us to add products to the cart again. Hope this helps somebody else one day for similar issue.

Well, most important thing to notice is your products must have positive stock, they must be enabled and there visibility should becatalog search. Assuming you have this run

php bin/magento indexer: reindex 

and notice the time it takes. This should pretty much fix the issue. I insist on checking all of above parameters because you say the urls exist in url_rewrite table. What happens when you search for the product in search bar? Let's say this doesn't fix your issue. You can export the products using magento backend. Then change the url key column for each row... May be you can append product sku... Then reimport. If import fails you will know the problem. If it doesn't then your problem will be solved. I'm curious as to how you directly imported data into database because there are so many foreign key constraints..