1 Reply Latest reply on Nov 5, 2010 3:05 PM by Ben Speckhard-Oracle

    Set column to nullable on version-enabled table

    Pedro Lourenço
      Hello,

      I have a table that is version-enabled and i want to change a column from NOT NULL to NULLABLE.
      When i try to do the operation, i get the error "ORA-01451: column to be modified to NULL cannot be modified to NULL".
      The code i'm running is something like this:

      -- Create test tables

      create table test1 (
      id number(1),
      something varchar2(50)
      );

      create table test2 (
      id number(1),
      other_id number(1) NOT NULL
      );

      -- Create PKs and FK
      alter table test1 add constraint test1_pk primary key (id);
      alter table test2 add constraint test2_pk primary key (id);
      alter table test2 add CONSTRAINT test2_fk foreign key (other_id) references test1 (id);

      -- Version-enable both tables
      begin
      dbms_wm.enableversioning('test1,test2');
      end;

      -- Start DDL session
      begin
      dbms_wm.beginddl('test2');
      dbms_wm.beginddl('test1');
      end;

      -- Modify column
      alter table test2_lts modify (other_id number(1) null);

      -- Commit DDL session
      begin
      dbms_wm.commitddl('test2');
      dbms_wm.commitddl('test1');
      end;
      (...)

      Looking at the possible causes of this error:
      (1) - column already allows NULL values;
      (2) - the column is used in a primary key or check constraint and can not accept NULL values;

      When i get the details for table TEST2_LTS, the column 'other_id' is already nullable, so the problem is obviously (1).
      However, column 'other_id' is set to NOT NULL in table TEST2_LT.
      I also tried the opposite operation - start with field nullable and then set to not null - and there is no problem.

      Why this difference occurs? Is supposed to happen? How can I solve this problem?
      Thanks.
      Best regards,

      Pedro Lourenço
        • 1. Re: Set column to nullable on version-enabled table
          Ben Speckhard-Oracle
          Hi,

          Yes, this is to be expected. The current version of Workspace Manager only has support for check constraints, not the null/not null keyword. Check out my 1st response to the following thread, as it is related.
          Re: Why is null field blank in describe of version-enabled table?

          Essentially, you can do both operations currently, you just need to operate on the check constraints themselves. After beginDDL is called, you would want to drop the check constraint from the _LTS table, instead of declaring the column not null.  After calling commitDDL, the constraint would then be removed from the base table.  Similarly, currently when adding the constraint, only the check constraint is actually being transferred.  This is sufficient to ensure that that data is not nullable, but the column would continue to appear as nullable by the SQL*Plus describe command and the tabcolumns views.

          Support for the null/not null keywords should be added in a future version.

          Regards,
          Ben
          1 person found this helpful