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>