Removing a NOT NULL constraint on a table

Solution:
ALTER TABLE [TABLE] MODIFY [COLUMN] VARCHAR2(20) NULL

Context:
We had the following situation: we had a table that has a NOT NULL constraint on one of the columns and we wanted to remove it.

As Java developers we were not well versed in how to add and remove constraints on an Oracle database. We know how to do the regular stuff, but when it comes time to do administration type things we need to do some research or get help from our DBAs.

We asked the DBAs how we can remove the NOT NULL constraint on a table. The DBA said that it would be tricky because she removes constraints by name. The constraint names are globally unique for each database instance.

Each developer is running a separate database instance. The test and production databases are also their own instances. We have a set of scripts that set up our database schema with each build. Since our intention is to promote these scripts all the way to production; ideas like dropping a table and adding it, aren't met with much enthusiasm.

The direction from our DBA was to remove the constraint by finding the constraint name in the USER_CONSTRAINTS table.

I think that would have worked if there weren't 5 not null constraints on the table in question. There were 5 constraints on that table that only differed in the SEARCH_CONDITION field. SEARCH_CONDITION is a long data type. Oracle does not allow using data from a long in where clauses. They do allow IS NULL and IS NOT NULL in where clauses on long data types. I did see that the other constraints on that table all had a null search condition. I thought about how I could solve this dilemma: I could wipe out all the NOT_NULL constraints and then add the ones I wanted. There were only 5 of them on this table.

I thought about using something like: DELETE FROM USER_CONSTRAINTS WHERE SEARCH_CONDITION IS NOT NULL.

I then wondered how I'd add the constraints back. That seemed really messy. I also ran across a DBA website that had sql that looked a lot like what I was thinking, but it looked like they were using it as a punchline.

I thought I might need to take a different tact. So I prayed to google--oracle add constraint not null

Hallelujah answer number 5.

That's when I found this wonderful page. Thank you Frozenmist from Bangalore. You made my afternoon.