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>