Why can't i put null into a nullable column

Today i had an email that data couldn't be inserted into a table when a certain column was null, could i please change that column to be nullable. Easy enough i thought - however when i came to look at the table definition the column already was nullable....

Are you sure that't the right table name?

Yes

Hmm... show me the error code

ORA-02290: check constraint (SYS.SYS_C001440057) violated

Ah....

So it's not a not null constraint thats the problem. Lets generate the ddl for the table and see what the issue is


CREATE TABLE "USER_EET_MARKET_DATA_PAR_MODEL" (  "INDEX_NAME" VARCHAR2(32),UNIT_NAME varchar2(32),
 CHECK ("INDEX_NAME" IS NOT NULL) VALIDATE  ) ;

So a not null check is implemented in a check constraint - brilliant. Nice design....

This now got more fiddly to fix as i can't just script up some code to alter the table, i need to find the correct constraint name (which is different in every environment) and drop that. To make matters worse the damn check constraint 'text' is in a f'in long column.

Here is what i did to make the code 'dynamic' (and i had to do it this way because the stupid to_lob function doesn't work in all statements - why they implemented it this way I've no idea - other than to force people away from longs.....)

Firstly we create a temp table to just store the lob version of the long columns i want to query

create global temporary table fixit
(constraint_name varchar2(4000),search_condition clob);


Then we populate it with this insert (where to_lob does work)

insert into fixit select constraint_name,to_lob(SEARCH_CONDITION) from user_constraints where table_name='USER_EET_MARKET_DATA_PAR_MODEL';

Then we dynamically generate a drop constraint statement

select 'alter table USER_EET_MARKET_DATA_PAR_MODEL drop constraint '||constraint_name||' ;'
from fixit where search_condition  like '%INDEX%'
/

This creates our alter statement which we then run to tidy up.

alter table USER_EET_MARKET_DATA_PAR_MODEL drop constraint SYS_C001440055 ;