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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *