I honestly can’t tell you how many times the root cause of a problem has been records in a table get repeated, where there should never be duplicates. I’ve perfected my method of removing the duplicates, but when it comes time to prevent them in the future, developers scoff.
Maybe it has to do with the fact that the fix, would throw an error when the developer doesn’t test to see if their new record is really new, or simply an update to an existing record.
Maybe it has to do with the fact the developer needs to get better acquainted with the relationships between tables in the database.
It could be the developer is simply unnerved by not understanding the utility a UNIQUE CONSTRAINT can bring to the table.
All I’m saying is the next time you remove duplicates from a table, take that opportunity to have a discussion with your developer. Make sure you both understand if duplicates are acceptable in the table.
If they are, then take the opportunity to discuss the DISTINCT or GROUP BY clauses for removing the duplicates.
But if those duplicates are not acceptable, and they never will be acceptable, take the time to discuss UNIQUE CONSTRAINTS. Get your developer up to date on how they can prevent the duplicates from being created in the future. Help them work through their concerns. By working with your developers you’ll grow the relationship between your two departments, and in the long term have a greater chance of buy-in when you ask for the really difficult changes.