Following Best Practices

Most of the time, I do follow the "Best Practices" while coding and designing in the Oracle Database. But sometime you need to work on someone else’s design that’s when you find it very difficult to change.

Here is a situation, Someone has created table in the past without proper Constraint names and due to this Oracle has created constraints with system names for ex. SYS_XXXX.

Now the requirement is to modify the Constraint to include another column as part of Primary Key. The Problem is, As constraint name is different in all database environments i.e. DEV, INT, UAT and PROD. You can not execute same release Sql script in all environments.
For. Ex.
ALTER TABLE EMP DROP CONSTRAINT SYS_CXXXXXXXX;

This command can’t not be executed in all environments as every environment might have different constraint name. As matter of fact, accidentally It might delete another useful constraint.

Thankfully, This time I found alternate solution as It was Primary Key, So I could use following command Instead of using constraint name.

ALTER TABLE EMP DROP PRIMARY KEY;

That’s why The "Best Practices" are so Important in real life.