Conference orarep::nomahs::sql

3878.0. "ALTER COLUMN doesn't check for constraint" by ukvms3.uk.oracle.com::LWILES (Louise Wiles, UK Rdb support) Wed Apr 09 1997 08:49

    Rdb7
    
    When a column with a constraint & an index was altered in V6.1 &
    below, the constraint & the index both had to be dropped before the
    alter would go through.
    
    With V7, only the index has to be dropped.
    
    Is there a reason for this change? I can't find this documented
    anywhere.
    
    The log below shows waht I mean.
    
    Thanks,
    Louise.
    
    V7.0:
    SQL> create database filename test;
    SQL> create table tab1 (a char constraint pk primary key);
    SQL> create index ind1 on tab1 (a) type is sorted;
    SQL> alter table tab1 alter column a char(2);
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-FLDINUSE, field A is referenced in index IND1
    -RDMS-F-RELFLDNOC, field A in relation TAB1 has not been changed
    SQL> drop index ind1;
    SQL> alter table tab1 alter column a char(2);
    SQL>
    
    V6.1:
    SQL> create database filename test;
    SQL> create table tab1 (a char constraint pk primary key);
    SQL> create index ind1  on tab1 (a) type is sorted;
    SQL> alter table tab1 alter column a char(2);
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-FLDINCON, field A is referenced in constraint PK
    -RDMS-F-RELFLDNOC, field A in relation TAB1 has not been changed
    SQL> alter table tab1 drop constraint pk;
    SQL> alter table tab1 alter column a char(2);
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-FLDINUSE, field A is referenced in index IND1
    -RDMS-F-RELFLDNOC, field A in relation TAB1 has not been changed
    SQL> drop index ind1;
    SQL> alter table tab1 alter column a char(2);
    SQL>

Check the Rdb7 Release Notes

    3.2.53 Now Can Modify Data Types When Constraints Are Defined

          Since Oracle Rdb Version 4.2, the SQL ALTER statement
          failed when it tried to update the data type of a column
          and a constraint existed that referred to the column.
          Oracle Rdb did not permit columns to be updated because
          the altered column might contain a value that would violate
          the constraint. 

		...etc...

Ian

    Thanks Ian.
    
    I needed to refine my search.
    
    Louise.