SQL$HELP_OLD72.HLB  —  ALTER  TABLE  Examples
    Example 1: Adding a column to the EMPLOYEES table

    SQL> ALTER TABLE EMPLOYEES ADD SALARY INTEGER(2);

    Example 2: Adding a column and altering a column in the COLLEGES
    table

    The following example adds two columns, one with a query name to
    the COLLEGES table. ALTER DOMAIN is also used to implicitly alter
    the POSTAL_CODE column to accept 9 characters instead of 5.

    SQL> SHOW TABLE COLLEGES;
    Information for table COLLEGES

    Comment on table COLLEGES:
    names and addresses of colleges attended by employees

    Columns for table COLLEGES:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    COLLEGE_CODE                    CHAR(4)          COLLEGE_CODE_DOM
     Primary Key constraint COLLEGES_PRIMARY_COLLEGE_CODE
    COLLEGE_NAME                    CHAR(25)         COLLEGE_NAME_DOM
    CITY                            CHAR(20)         CITY_DOM
    STATE                           CHAR(2)          STATE_DOM
    POSTAL_CODE                     CHAR(5)          POSTAL_CODE_DOM
       .
       .
       .
    SQL> ALTER TABLE COLLEGES
    cont>   ADD RANKING INTEGER
    cont>   ADD NUMBER_ALUMS INTEGER
    cont>           QUERY_NAME IS 'ALUMS';
    SQL> ALTER DOMAIN POSTAL_CODE_DOM CHAR(9);
    SQL> SHOW TABLE COLLEGES;

    Information for table COLLEGES

    Comment on table COLLEGES:
    names and addresses of colleges attended by employees

    Columns for table COLLEGES:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    COLLEGE_CODE                    CHAR(4)          COLLEGE_CODE_DOM
     Primary Key constraint COLLEGES_PRIMARY_COLLEGE_CODE
    COLLEGE_NAME                    CHAR(25)         COLLEGE_NAME_DOM
    CITY                            CHAR(20)         CITY_DOM
    STATE                           CHAR(2)          STATE_DOM
    POSTAL_CODE                     CHAR(9)          POSTAL_CODE_DOM
    RANKING                         INTEGER
    NUMBER_ALUMS                    INTEGER
     Query Name:    ALUMS
       .
       .
       .

    Example 3: Adding and modifying default values

    SQL> /* Add a default value to the column HOURS_OVERTIME
    ***> */
    SQL> create table DAILY_SALES
    cont>     (hours_overtime     int
    cont>     ,hours_worked       int default 0
    cont>     ,gross_sales        int
    cont>     ,salesperson        char(20)
    cont>     );
    SQL>
    SQL> /* Change the default value for the column HOURS_OVERTIME
    ***> */
    SQL> alter table DAILY_SALES
    cont>     alter column HOURS_OVERTIME
    cont>   set default 0;
    SQL>
    SQL> /* Insert the days sales figures into the table,
    ***>    accepting the default values for HOURS_WORKED, and
    ***>    HOURS_OVERTIME
    ***> */
    SQL> insert into DAILY_SALES (gross_sales, salesperson)
    cont>     values (2567, 'Bartlett');
    1 row inserted
    SQL>
    SQL> table DAILY_SALES;
     HOURS_OVERTIME   HOURS_WORKED   GROSS_SALES   SALESPERSON
                  0              0          2567   Bartlett
    1 row selected
    SQL>

    Example 4: Deleting a constraint from the EMPLOYEES table

    To find out the name of a constraint, use the SHOW TABLES
    statement. The SHOW TABLES statement shows all constraints that
    refer to a table, not just those defined as part of the table's
    definition. For that reason it is good practice to always use a
    prefix to identify the table associated with a constraint when
    you assign constraint names with the CONSTRAINT clause.

    The constraint DEGREES_FOREIGN1 in this SHOW display follows that
    convention to indicate that the constraint is associated with
    the DEGREES, not the EMPLOYEES, table despite the constraint's
    presence in the EMPLOYEES display.

    SQL> SHOW TABLE EMPLOYEES
    Information for table EMPLOYEES

    Comment on table EMPLOYEES:
    personal information about each employee

    Columns for table EMPLOYEES:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    EMPLOYEE_ID                     CHAR(5)          ID_DOM
     Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
    LAST_NAME                       CHAR(14)         LAST_NAME_DOM
    FIRST_NAME                      CHAR(10)         FIRST_NAME_DOM
    MIDDLE_INITIAL                  CHAR(1)          MIDDLE_INITIAL_DOM
    ADDRESS_DATA_1                  CHAR(25)         ADDRESS_DATA_1_DOM
    ADDRESS_DATA_2                  CHAR(20)         ADDRESS_DATA_2_DOM
    CITY                            CHAR(20)         CITY_DOM
    STATE                           CHAR(2)          STATE_DOM
    POSTAL_CODE                     CHAR(5)          POSTAL_CODE_DOM
    SEX                             CHAR(1)          SEX_DOM
    BIRTHDAY                        DATE             DATE_DOM
    STATUS_CODE                     CHAR(1)          STATUS_CODE_DOM

    Table constraints for EMPLOYEES:
    EMPLOYEES_PRIMARY_EMPLOYEE_ID
     Primary Key constraint
     Column constraint for EMPLOYEES.EMPLOYEE_ID
     Evaluated on COMMIT
     Source:
            EMPLOYEES.EMPLOYEE_ID PRIMARY KEY

    EMP_SEX_VALUES
     Check constraint
     Table constraint for EMPLOYEES
     Evaluated on COMMIT
     Source:
            CHECK          (
                             SEX IN ('M', 'F', '?')
                             )

    EMP_STATUS_CODE_VALUES
     Check constraint
     Table constraint for EMPLOYEES
     Evaluated on COMMIT
     Source:
            CHECK          (
                             STATUS_CODE IN ('0', '1', '2', 'N')
                             )

    Constraints referencing table EMPLOYEES:
    DEGREES_FOREIGN1
     Foreign Key constraint
     Column constraint for DEGREES.EMPLOYEE_ID
     Evaluated on COMMIT
     Source:
            DEGREES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)

    JOB_HISTORY_FOREIGN1
     Foreign Key constraint
     Column constraint for JOB_HISTORY.EMPLOYEE_ID
     Evaluated on COMMIT
     Source:
            JOB_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)

    RESUMES_FOREIGN1
     Foreign Key constraint
     Column constraint for RESUMES.EMPLOYEE_ID
     Evaluated on COMMIT
     Source:
            RESUMES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)

    SALARY_HISTORY_FOREIGN1
     Foreign Key constraint
     Column constraint for SALARY_HISTORY.EMPLOYEE_ID
     Evaluated on COMMIT
     Source:
            SALARY_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
       .
       .
       .
    SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_SEX_VALUES;

    Example 5: Adding a NOT NULL constraint to the EMPLOYEES table

    SQL> ALTER TABLE EMPLOYEES
    cont>   ALTER BIRTHDAY
    cont>   CONSTRAINT E_BIRTHDAY_NOT_NULL
    cont>   NOT NULL;

    If any rows in the EMPLOYEES table have a null BIRTHDAY column,
    the ALTER statement fails and none of the changes described in it
    will be made.

    Example 6: Altering the character set of a table column

    Assume the database was created specifying the database default
    character set and identifier character set as DEC_KANJI and the
    national character set as KANJI. Also assume the ROMAJI column
    was created in the table COLOURS specifying the identifier
    character set.

    SQL> SET CHARACTER LENGTH 'CHARACTERS';
    SQL> SHOW TABLE (COLUMNS) COLOURS;
    Information for table COLOURS

    Columns for table COLOURS:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    ENGLISH                         CHAR(8)          MCS_DOM
             DEC_MCS 8 Characters,  8 Octets
    FRENCH                          CHAR(8)          MCS_DOM
             DEC_MCS 8 Characters,  8 Octets
    JAPANESE                        CHAR(4)          KANJI_DOM
             KANJI 4 Characters,  8 Octets
    ROMAJI                          CHAR(8)          DEC_KANJI_DOM
    KATAKANA                        CHAR(8)          KATAKANA_DOM
             KATAKANA 8 Characters,  8 Octets
    HINDI                           CHAR(8)          HINDI_DOM
             DEVANAGARI 8 Characters,  8 Octets
    GREEK                           CHAR(8)          GREEK_DOM
             ISOLATINGREEK 8 Characters,  8 Octets
    ARABIC                          CHAR(8)          ARABIC_DOM
             ISOLATINARABIC 8 Characters,  8 Octets
    RUSSIAN                         CHAR(8)          RUSSIAN_DOM
             ISOLATINCYRILLIC 8 Characters,  8 Octets

    SQL> ALTER TABLE COLOURS ALTER ROMAJI NCHAR(8);
    SQL> SHOW TABLE (COLUMNS) COLOURS;
    Information for table COLOURS

    Columns for table COLOURS:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    ENGLISH                         CHAR(8)          MCS_DOM
             DEC_MCS 8 Characters,  8 Octets
    FRENCH                          CHAR(8)          MCS_DOM
             DEC_MCS 8 Characters,  8 Octets
    JAPANESE                        CHAR(4)          KANJI_DOM
             KANJI 4 Characters,  8 Octets
    ROMAJI                          CHAR(8)
             KANJI 8 Characters,  16 Octets
    KATAKANA                        CHAR(8)          KATAKANA_DOM
             KATAKANA 8 Characters,  8 Octets
    HINDI                           CHAR(8)          HINDI_DOM
             DEVANAGARI 8 Characters,  8 Octets
    GREEK                           CHAR(8)          GREEK_DOM
             ISOLATINGREEK 8 Characters,  8 Octets
    ARABIC                          CHAR(8)          ARABIC_DOM
             ISOLATINARABIC 8 Characters,  8 Octets
    RUSSIAN                         CHAR(8)          RUSSIAN_DOM
             ISOLATINCYRILLIC 8 Characters,  8 Octets

    SQL>

    Example 7: Error displayed if table COLOURS contains data

    In the following example, the column ROMAJI is defined with the
    DEC_KANJI character set. If the column ROMAJI contains data
    before you alter the character set of the column, SQL displays
    the following error when you try to retrieve data after altering
    the table.

    SQL> SELECT ROMAJI FROM COLOURS;
    %RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
    -RDMS-E-CSETBADASSIGN, incompatible character sets prohibits the requested
     assignment
    SQL> --
    SQL> -- To recover, use the ROLLBACK statement or return the column to its
    SQL> -- original character set.
    SQL> --
    SQL> ROLLBACK;
    SQL> SELECT ROMAJI FROM COLOURS;
     ROMAJI
     kuro
     shiro
     ao
     aka
     ki
     midori
    6 rows selected
    SQL>

    Example 8: Using the Position Clause

    SQL> SHOW TABLE (COL) EMPLOYEES
    Information for table EMPLOYEES
    Columns for table EMPLOYEES:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    EMPLOYEE_ID                     CHAR(5)          ID_NUMBER
     Missing Value:
    LAST_NAME                       CHAR(14)         LAST_NAME
    FIRST_NAME                      CHAR(10)         FIRST_NAME
    MIDDLE_INITIAL                  CHAR(1)          MIDDLE_INITIAL
     Missing Value:
    ADDRESS_DATA_1                  CHAR(25)         ADDRESS_DATA_1
     Missing Value:
    ADDRESS_DATA_2                  CHAR(25)         ADDRESS_DATA_2
     Missing Value:
    CITY                            CHAR(20)         CITY
     Missing Value:
    STATE                           CHAR(2)          STATE
     Missing Value:
    POSTAL_CODE                     CHAR(5)          POSTAL_CODE
     Missing Value:
    SEX                             CHAR(1)          SEX
     Missing Value: ?
    BIRTHDAY                        DATE VMS         STANDARD_DATE
     Missing Value: 17-NOV-1858 00:00:00.00
    STATUS_CODE                     CHAR(1)          STATUS_CODE
     Missing Value: N
    SQL> -- Alter the table to rearrange the order in which columns
    SQL> -- are displayed.
    SQL> ALTER TABLE EMPLOYEES
    cont> ALTER COLUMN SEX BEFORE COLUMN LAST_NAME
    cont> ALTER COLUMN BIRTHDAY BEFORE COLUMN LAST_NAME
    cont> ALTER COLUMN STATUS_CODE BEFORE COLUMN LAST_NAME;
    SQL> COMMIT;
    SQL> -- Show the table to demonstrate that the order in which
    SQL> -- columns are displayed has changed.
    SQL> SHOW TABLE (COL) EMPLOYEES;
    Information for table EMPLOYEES
    Columns for table EMPLOYEES:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    EMPLOYEE_ID                     CHAR(5)          ID_NUMBER
     Missing Value:
    SEX                             CHAR(1)          SEX
     Missing Value: ?
    BIRTHDAY                        DATE VMS         STANDARD_DATE
     Missing Value: 17-NOV-1858 00:00:00.00
    STATUS_CODE                     CHAR(1)          STATUS_CODE
     Missing Value: N
    LAST_NAME                       CHAR(14)         LAST_NAME
    FIRST_NAME                      CHAR(10)         FIRST_NAME
    MIDDLE_INITIAL                  CHAR(1)          MIDDLE_INITIAL
     Missing Value:
    ADDRESS_DATA_1                  CHAR(25)         ADDRESS_DATA_1
     Missing Value:
    ADDRESS_DATA_2                  CHAR(25)         ADDRESS_DATA_2
     Missing Value:
    CITY                            CHAR(20)         CITY
     Missing Value:
    STATE                           CHAR(2)          STATE
     Missing Value:
    POSTAL_CODE                     CHAR(5)          POSTAL_CODE
     Missing Value:

    Example 9: Disabling a Trigger

    SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
     EMPLOYEE_ID   JOB_CODE   JOB_START     JOB_END       DEPARTMENT_CODE
       SUPERVISOR_ID
     00164         DMGR       21-Sep-1981   NULL          MBMN
       00228
     00164         SPGM        5-Jul-1980   20-Sep-1981   MCBM
       00164
    2 rows selected
    SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
    1 row deleted
    SQL> -- Show that the EMPLOYEE_ID_CASCADE_DELETE trigger caused
    SQL> -- records in the JOB_HISTORY table to be deleted for the
    SQL> -- employee with EMPLOYEE_ID of 00164.
    SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
    0 rows selected
    SQL> -- Roll back the delete operation and alter the EMPLOYEES table
    SQL> -- to disable the EMPLOYEE_ID_CASCADE_DELETE trigger.
    SQL> ROLLBACK;
    SQL> ALTER TABLE EMPLOYEES
    cont> DISABLE TRIGGER EMPLOYEE_ID_CASCADE_DELETE;
    SQL> -- Commit the alter operation and disconnect to ensure that
    SQL> -- the next connection will have the trigger disabled.
    SQL> COMMIT;
    SQL> DISCONNECT DEFAULT;
    SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
    SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
    1 row deleted
    SQL> -- Show that with the trigger disabled, a deletion of
    SQL> -- employee 00164 from the EMPLOYEES table does not
    SQL> -- trigger a deletion for that employee from the
    SQL> -- JOB_HISTORY table.
    SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
    EMPLOYEE_ID   JOB_CODE   JOB_START     JOB_END       DEPARTMENT_CODE
       SUPERVISOR_ID
     00164         DMGR       21-Sep-1981   NULL          MBMN
       00228
     00164         SPGM        5-Jul-1980   20-Sep-1981   MCBM
       00164
    2 rows selected

    Example 10: NOT NULL constraint is dropped

    The following example shows that the NOT NULL constraint is
    dropped by ALTER TABLE.

    SQL> create table MY_TABLE (a integer not null);
    SQL>
    SQL> show table (constraint) MY_TABLE
    Information for table MY_TABLE

    Table constraints for MY_TABLE:
    MY_TABLE_A_NOT_NULL
     Not Null constraint
     Column constraint for MY_TABLE.A
     Evaluated on UPDATE, NOT DEFERRABLE
     Source:
            MY_TABLE.A NOT null

    Constraints referencing table MY_TABLE:
    No constraints found

    SQL>
    SQL> alter table MY_TABLE
    cont>     alter column A NULL;
    SQL>
    SQL> show table (constraint) MY_TABLE
    Information for table MY_TABLE

    Table constraints for MY_TABLE:
    No constraints found

    Constraints referencing table MY_TABLE:
    No constraints found

    SQL>

    Example 11: Adding an identity column to an existing table

    SQL> alter table EMPLOYEES
    cont>   add column SEQUENCE_ID integer identity (1000, 10)
    cont>   comment is 'Add unique sequence number for every employee';
    SQL>
    SQL> show table (column) EMPLOYEES
    Information for table EMPLOYEES

    Columns for table EMPLOYEES:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    EMPLOYEE_ID                     CHAR(5)          ID_NUMBER
    .
    .
    .
    SEQUENCE_ID                     INTEGER
     Computed:      IDENTITY
     Comment:       Add unique sequence number for every employee

    SQL> select EMPLOYEE_ID, SEQUENCE_ID from employees;
     EMPLOYEE_ID   SEQUENCE_ID
     00164                1000
     00165                1010
    .
    .
    .
     00418                1970
     00435                1980
     00471                1990
    100 rows selected
    SQL>
    SQL> show sequence EMPLOYEES
         EMPLOYEES
     Sequence Id: 2
     Initial Value: 1000
     Minimum Value: 1000
     Maximum Value: (none)
     Next Sequence Value: 2000
     Increment by: 10
     Cache Size: 20
     No Order
     No Cycle
     No Randomize
     Wait
     Comment:       column IDENTITY sequence
    SQL>

    Example 12: Revising a COMPUTED BY column

    SQL> create table ttt (a integer, c computed by CURRENT_USER);
    SQL> insert into ttt (a) values (10);
    1 row inserted
    SQL> select * from ttt;
               A   C
              10   SMITH
    1 row selected
    SQL>
    SQL> show table (column) ttt
    Information for table TTT

    Columns for table TTT:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    A                               INTEGER
    C                               CHAR(31)
             UNSPECIFIED 31 Characters,  31 Octets
     Computed:       by CURRENT_USER

    SQL>
    SQL> alter table ttt
    cont>     alter c
    cont>     computed by upper (substring (current_user from 1 for 1))
    cont>           || lower (substring (current_user from 2));
    SQL>
    SQL> show table (column) ttt
    Information for table TTT

    Columns for table TTT:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    A                               INTEGER
    C                               VARCHAR(31)
             UNSPECIFIED 31 Characters,  31 Octets
     Computed:       by upper (substring (current_user from 1 for 1))
                    || lower (substring (current_user from 2))

    SQL>
    SQL> select * from ttt;
               A   C
              10   Smith
    1 row selected
    SQL>
Close Help