Category Archives: oracle

Always name your constraints in Oracle

In Oracle, when you create a constraint (like a primary key), you can either provide a name or have Oracle generate one for you. I got lazy on a recent project, and had Oracle generate the constraint names for the primary keys on my new tables. Later, I had to come back and change the key structure and it bit me. What I like to do is keep a set of DDL files in my project to update the database schema as it evolves. When you want to change the structure, you create a new DDL file and put in your ALTER TABLE statements. You apply it to the dev database, then when you are satisfied, you apply it to QA and eventually production. This may vary depending how many environments you have, but the idea is a consistent set of database updates across environments.

Anyway, today I had to drop the primary keys and create new ones, so I ended up with statements like this:

ALTER TABLE CRITICAL_COURSE_REQUIREMENT DROP CONSTRAINT SYS_C0031831;
ALTER TABLE CRITICAL_COURSE_REQUIREMENT ADD CONSTRAINT CRITICAL_COURSE_REQUIREMENT_PK PRIMARY KEY (CATALOG_TERM, ACADEMIC_PLAN, SUBJECT, CATALOG_NBR);

As you can see, the DDL is then dependent on the runtime state of the Oracle database at the time the original PK was added. This sucks, because it will end up with a different name in each environment. So, in the new PK, I gave it a name (CRITICAL_COURSE_REQUIREMENT_PK) which will be consistent across environments next time I need to change it.