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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s