ALTER
Week #2: Articles
Learning Outcomes
- Learn how the data dictionary manages tables, columns, and constraints.
- Learn how to drop tables.
- Learn how to drop indexes.
- Learn how to drop views.
- Learn how to drop types.
CREATEStatementALTERStatementDROPStatementRENAMEStatementTRUNCATEStatementCOMMENTStatement
ALTER Statement
You can apply the ALTER statement to many objects in the database. For example, you can change databases, users, tables, indexes, sequences, synonyms, types, view, and directories. This post focuses on changing columns and constraints of tables.
Tables & the Data Dictionary
This section shows you how to discover and work with the data dictionary to understand how tables really work in the Oracle relational database.
Instruction Details →
Table definitions and constraints change over time for many reasons. These changes occur because developers discover more information about the business model, which requires changes to table definitions. This section examines how you can add, modify, and remove columns or constraints; rename tables, columns, or constraints; and drop tables.
First, however, you need to understand how table definitions are stored in the data catalogs. When you understand the rules of these structures and how they’re stored, you can appreciate why SQL is able to let you change so much, so easily.
Data Catalog Table Definitions The data catalog stores everything by the numbers, which happens to be through surrogate primary keys. The database also maintains a unique index on object names, which means that you can use a name only once per schema in an Oracle database. This list of unique values is known as the schema’s namespace.
As discussed, Oracle maintains the data catalog in the sys schema and provides access to administrative views, which are prefaced by CDB_, DBA_, ALL_, and USER_. The USER_ prefix is available to every schema for objects that a user owns. The CDB_ prefix is for the new container database views in Oracle Database 12c and forward. The ALL_ and DBA_ prefixes give you access to objects owned by others, and only superusers or administrative users have access privileges to these views.
Many developer tools can easily display information from the data catalog views, such as Oracle SQL Developer, Quest’s Toad for Oracle, or Oracle CASE tools. Sometimes you need to explore a database for specific information, and the fastest way would be to launch a few quick queries against the data catalog. The catalog view that lets you explore column definitions, including for invisible columns, is the USER_TAB_COLUMNS view. The catalog view that lets you explore column definitions only for visible columns is the USER_TAB_COLS view. Another catalog USER_TAB_COLS view lets you explore only visible columns (short for table’s columns).
The following query leverages some SQL*Plus formatting to let you find the definition of a specific table and display it in a single-page format:
SQL> COLUMN column_id FORMAT 999 HEADING "Column|ID #" SQL> COLUMN table_name FORMAT A12 HEADING "Table Name" SQL> COLUMN column_name FORMAT A18 HEADING "Column Name" SQL> COLUMN data_type FORMAT A10 HEADING "Data Type" SQL> COLUMN csize FORMAT 999 HEADING "Column|Size" SQL> SELECT utc.column_id 2 , utc.table_name 3 , utc.column_name 4 , utc.data_type 5 , NVL(utc.data_length,utc.data_precision) AS csize 6 FROM user_tab_columns utc 7 WHERE utc.table_name = 'CONTACT'; |
The table name is in uppercase because Oracle maintains metadata text in an uppercase string. You could replace line 7 with the following line that uses the UPPER function to promote the text case before comparison, if you prefer to type table names and other metadata values in lowercase or mixed case:
7 WHERE utc.table_name = UPPER('contact'); |
It displays the following:
Column Column
ID # Table Name Column Name Data Type Size
------ ------------ ------------------ ---------- ------
1 CONTACT CONTACT_ID NUMBER 22
2 CONTACT MEMBER_ID NUMBER 22
3 CONTACT CONTACT_TYPE NUMBER 22
4 CONTACT FIRST_NAME VARCHAR2 20
5 CONTACT MIDDLE_NAME VARCHAR2 20
6 CONTACT LAST_NAME VARCHAR2 20
7 CONTACT CREATED_BY NUMBER 22
8 CONTACT CREATION_DATE DATE 7
9 CONTACT LAST_UPDATED_BY NUMBER 22
10 CONTACT LAST_UPDATE_DATE DATE 7 |
The column_id value identifies the position of columns for INSERT statements. The ordered list is set when you define a table with the CREATE TABLE statement or is reset when you modify it with an ALTER TABLE statement. Columns keep the position location when you change the columns’ name or data type. Columns lose their position when you remove them from a table’s definition, and when you add them back, they appear at the end of the positional list of values. There’s no way to shift their position in an Oracle database without dropping and re-creating the table.
Each column has a data type that defines its physical size. The foregoing example shows that NUMBER data types take up to 22 characters, the strings take 20 characters, and the dates take 7 characters. As you learned in Chapter 4, numbers can also have a specification (the values to the right of the decimal point) that fits within the maximum length (or precision) of the data type.
The USER_CONSTRAINTS and USER_CONS_COLUMNS views hold information about constraints. The USER_CONSTRAINTS view holds the descriptive information about the type of constraint, and the USER_CONS_COLUMNS view holds the list of columns participating in the constraint.
You would use a query like this to discover constraints and columns (formatting provided by the SQL*Plus commands):
SQL> COLUMN table_name FORMAT A12 HEADING "Table Name" SQL> COLUMN constraint_name FORMAT A16 HEADING "Constraint|Name" SQL> COLUMN position FORMAT A8 HEADING "Position" SQL> COLUMN column_name FORMAT A18 HEADING "Column Name" SQL> SELECT ucc.table_name 2 , ucc.constraint_name 3 , uc.constraint_type ||':'|| ucc.position AS position 4 , ucc.column_name 5 FROM user_constraints uc JOIN user_cons_columns ucc 6 ON uc.table_name = ucc.table_name 7 AND uc.constraint_name = ucc.constraint_name 8 WHERE ucc.table_name = 'CONTACT' 9 ORDER BY ucc.constraint_name 10 , ucc.position |
It would produce the following output:
Constraint Table Name Name Position Column Name ------------ ---------------- -------- ------------------ CONTACT FK_CONTACT_1 R:1 MEMBER_ID CONTACT FK_CONTACT_2 R:1 CONTACT_TYPE CONTACT FK_CONTACT_3 R:1 CREATED_BY CONTACT FK_CONTACT_4 R:1 LAST_UPDATED_BY CONTACT NN_CONTACT_1 C: MEMBER_ID CONTACT NN_CONTACT_2 C: CONTACT_TYPE CONTACT NN_CONTACT_3 C: FIRST_NAME CONTACT NN_CONTACT_4 C: LAST_NAME CONTACT NN_CONTACT_5 C: CREATED_BY CONTACT NN_CONTACT_6 C: CREATION_DATE CONTACT NN_CONTACT_7 C: LAST_UPDATED_BY CONTACT NN_CONTACT_8 C: LAST_UPDATE_DATE CONTACT PK_CONTACT_1 P:1 CONTACT_ID |
The first four lines of output report four referential integrity constraints. The next eight lines of output report eight check constraints, which you should recognize from the “NN_” prefix as NOT NULL constraints. The last line of output reports the primary key constraint on the CONTACT_ID column of the table.
ALTER table columns
This section shows you how to add columns to, modify columns in, and drop columns from a table.
Instruction Details →
Database tables should be designed to hold a single subject, and when they hold a single subject, they help you normalize information that supports business, research, or engineering processes. In the real world, requirements change, and eventually modifying a table becomes necessary. Some of these changes are relatively trivial, such as changing a column name or data type. Some changes are less trivial, such as when some descriptive item (column) is overlooked, a column isn’t large enough to hold a large number or string, or a column of data needs to be renamed or moved to another column. You can make any of these changes using the ALTER TABLE statement.
More involved changes occur in three situations:
- When you must change a column’s data type when it contains rows of data
- When you rename a column when existing SQL statements already use the older column
- When you shift the position of adjoining columns that share the same data type
When you need to change the data type of a column that contains data, you need to develop a data migration plan. Small-scale data migration might entail adding a new column, moving the contents from one column to the other, and then dropping the original column. Unless the database supports an implicit casting operation from the current to future data type, you will need a SQL statement to change the data type explicitly and put it into the new column.
Changing the name of a column in a table seems a trifling thing, but it’s not insignificant when application software uses that column name in SQL statements. Any change to the column name will break the application software. You need to identify all dependent code artifacts and ensure that they’re changed at the same time you make the changes to the column in the table. A full regression-testing plan needs to occur when columns are renamed in tables that support working application software. You can start by querying the ALL_, DBA_, or USER_DEPENDENCIES views that preserve dependencies about tables and stored program units.
Shifting the position of columns can have two different types of impacts. One potential impact is that you break INSERT statements that don’t use a column list before the VALUES clause or subquery. This happens when the columns have different data types, because the INSERT statements will fail and raise errors. The other potential impact is much worse, because it produces corrupt data. This happens when you change the position of two columns that share the same data type. The change doesn’t break INSERT statements in an easily detectable way, as did the other scenario, because it simply inverts the data into the wrong columns. The fix is the same as when you change the positions of columns that have different data types, but the fix depends on when you notice the problem and how much corrupt data you need to sanitize (fix).
These risks should be managed by your company’s release engineering team and should be subject to careful software configuration management of your code repository. You can leverage the CDB_, ALL_, DBA_, and USER_DEPENDENCIES views to check on dependencies in your software.
The prototypes discussed in the next two sections qualify how you make these types of changes in an Oracle database.
Column Maintenance The ALTER TABLE statement allows you to add columns or constraints, to modify properties of columns and constraints, and to drop columns or constraints. A number of DBA-type properties are excluded from the ALTER TABLE prototype, and the focus here is on those features that support relational tables.
Here’s the basic prototype for the ALTER TABLE statement:
ALTER TABLE [schema_name.]table_name [RENAME TO new_table_name] [READ ONLY] [READ WRITE] [{NO PARALLEL | PARALLEL n}] [ADD ({column_name data_type [SORT][DEFAULT VALUE][ENCRYPT KEY] | virtual_column_name} data_type [GENERATED][ALWAYS] AS (expression)} ,{column_name data_type [SORT][DEFAULT VALUE][ENCRYPT KEY] | virtual_column_name} data_type [GENERATED][ALWAYS] AS (expression)} [, ...])] [MODIFY ({column_name data_type [SORT][DEFAULT VALUE][ENCRYPT KEY] | virtual_column_name} data_type [GENERATED][ALWAYS] AS (expression)} ,{column_name data_type [SORT][DEFAULT VALUE][ENCRYPT KEY] | virtual_column_name} data_type [GENERATED][ALWAYS] AS (expression)} [, ...])] [DROP (column_name {CASCADE CONSTRAINTS | INVALIDATE} [CHECKPOINT n] ,column_name {CASCADE CONSTRAINTS | INVALIDATE} [CHECKPOINT n] [, ...])] [ADD [CONSTRAINT constraint_name] {PRIMARY KEY (column_name [,column_name [, ...]) | UNIQUE (column_name [,column_name [, ...]) | CHECK (check_condition) | FOREIGN KEY (column_name [,column_name [, ...]) REFERENCES table_name (column_name [,column_name [, ...])}] [MODIFY data_type [SORT][DEFAULT VALUE][ENCRYPT KEY] | virtual_column_name} data_type [GENERATED][ALWAYS] AS (expression)} [, ...])] [RENAME COLUMN old_column_name TO new_column_name] [RENAME CONSTRAINT old_constraint_name TO new_constraint_name] |
The following sections provide working examples that add, modify, rename, and drop columns and constraints. Notice that you don’t add a not null constraint to a column, but you modify the property of an existing column.
Adding columns and constraints
This section shows you how to add columns and constraints. It also provides some guidance on when you can constrain a column as not null.
Instruction Details →
Here’s how you add a new column to a table:
SQL> ALTER TABLE rental_item 2 ADD (rental_item_price NUMBER); |
If the table contains no data, you could also add the column with a not null constraint, like this:
SQL> ALTER TABLE rental_item ADD 2 (rental_item_price NUMBER CONSTRAINT nn1_rental_item NOT NULL ); |
Adding a column with a not null constraint fails when rows are included in the table because when you add the column, its values are empty in all the table rows. The attempt would raise the following error message:
ALTER TABLE rental_item
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column |
You can disable the constraint until you’ve entered any missing values and then you can re-enable the constraint. After you’ve added values to all rows of a nullable column, you can constrain the column to disallow null values. The syntax requires you to modify the column, like so:
SQL> ALTER TABLE rental_item MODIFY 2 (rental_item_price NUMBER CONSTRAINT nn1_rental_item NOT NULL ); |
You can also add more than one column at a time with the ALTER TABLE statement. The following would add two columns:
SQL> ALTER TABLE rental_item 2 ADD (rental_item_price NUMBER) 3 ADD (rental_item_type NUMBER); |
Notice that no comma appears between the two ADD clauses.
That’s it for columns. Now you’ll see how to add the four constraints that work with the ADD clause: primay key, check, not null, unique, and foreign key. Note that you can raise errors with these statements when you already have data in a table and it fails to meet the rule of the constraint.
All the following examples work when tables are empty or conform to the constraint rules. After all, what would be the point of a database constraint that didn’t constrain behaviors?
This example adds a primary key constraint to a surrogate key column. A primary key in this case restricts a single column’s behavior. Here’s the syntax:
SQL> ALTER TABLE calendar 2 ADD PRIMARY KEY (calendar_id); |
The alternative would be to add a primary key constraint on the natural key columns, like this:
SQL> ALTER TABLE calendar 2 ADD PRIMARY KEY (month_name, start_date, end_date); |
The check constraint is very powerful in Oracle, because it lets you enforce a single rule or a complex set of rules. In the following example, you add the column and then an out-of-line constraint on the new column:
SQL> ALTER TABLE calendar 2 ADD (month_type VARCHAR2(1)) 3 ADD CONSTRAINT ck_month_type 4 CHECK(month_type = 'S' AND month_shortname = 'FEB' 5 OR month_type = 'M' AND month_shortname IN ('APR','JUN','SEP') 6 OR month_type = 'L'); |
The check constraint verifies that a month_type value must correspond to a combination of its value and the value of the month_shortname column. Any month with less than 30 days holds an S (short), with 30 days holds an M (medium), and with 31 days holds an L (long).
The following unique constraint guarantees that no start_date and end_date combination can exist twice in a calendar table:
SQL> ALTER TABLE calendar 2 ADD CONSTRAINT un_california UNIQUE (start_date, end_date); |
A foreign key constraint works with surrogate or natural keys by referencing the table and column or columns that are in its primary key. The next example sets the two foreign keys in a translation table between the rental and item tables:
SQL> ALTER TABLE rental_item 2 ADD CONSTRAINT fk_rental_id_1 3 FOREIGN KEY (rental_id) REFERENCES rentals (rental_id) 4 ADD CONSTRAINT fk_rental_id_2 5 FOREIGN KEY (item_id) REFERENCES items (item_id); |
The next example sets a foreign key on the natural key of the contact table, as shown earlier in this chapter. Foreign keys composed of more than one column are composite foreign keys (outside of Oracle documentation, these may also be labeled as compound keys). This references three natural columns and one foreign key column:
SQL> ALTER TABLE delegate 2 ADD CONSTRAINT fk_natural_contact 3 FOREIGN KEY ( member_id, first_name, middle_name, last_name ) 4 REFERENCES contact (member_id, first_name, middle_name, last_name); |
The key concept is that you can add both column- and table-level (that is, multiple column) constraints with the ALTER TABLE statement. As shown, you can also add the column and then the constraint that goes with it.
Modifying columns and constraints
This section shows you how to modify columns and constraints.
Instruction Details →
Oracle lets you change column names, data types, and constraints with the ALTER TABLE statement. Although column names and data types change routinely during major software upgrades, these changes can and do cause problems because existing code can depend on the type or names of columns and encounter failures when they change unexpectedly.
The following examples demonstrate what you’re likely to encounter when working with modifying tables. The first example lets you change the name of a column:
SQL> ALTER TABLE calendar 2 RENAME COLUMN calendar_name TO full_month_name; |
It’s also possible to make more than one change with a single ALTER statement, like
SQL> ALTER TABLE calendar 2 ADD (month_name VARCHAR2(9)) 3 ADD (short_name VARCHAR2(3) CONSTRAINT calendar_nn1 NOT NULL); |
The ALTER statement supports a superuser, or a common user who has been granted the ALTER ANY TABLE privilege to change the column name of another schema’s table to change the column name of another schema’s table when they have the ALTER ANY TABLE privilege. While you can make multiple changes to columns in a single ALTER statement, there’s an exception to that rule when renaming multiple columns.
If you wanted to change the names of two or more columns in one ALTER STATEMENT, you would try something like this:
SQL> ALTER TABLE calendar 2 RENAME COLUMN calendar_name TO full_month_name 3 RENAME COLUMN calendar_short_name TO short_month_name; |
but that would fail and raise an ORA-23290 error:
ALTER TABLE calendar * ERROR at line 1: ORA-23290: This operation may not be combined with any other operation |
The failure occurs because you can’t combine a RENAME clause with any other clause in an ALTER TABLE statement. It’s simply disallowed, with no further elaboration provided in the Oracle Database Administrator’s Guide.
Data type changes are straightforward when the table contains no data, but you can’t change the type when data exists in the column. A quick example attempts to change a start_date column using a DATE data type to using a VARCHAR2 data type. The following syntax works when no data is included in the column but fails when data exists:
SQL> ALTER TABLE calendar 2 MODIFY (start_date VARCHAR2(9)); |
With data in the column, it raises this error message:
MODIFY (start_date VARCHAR2(9))
*
ERROR at line 2:
ORA-01439: column to be modified must be empty to change datatype |
You would add a not null constraint to the start_date column with the following DML
statement:
SQL> ALTER TABLE calendar 2 MODIFY (start_date DATE NOT NULL ); |
The only problem with the foregoing statement is that it creates a not null constraint with a system-generated name. The best practice would assign the constraint a name, like so:
SQL> ALTER TABLE calendar 2 MODIFY (start_date DATE CONSTRAINT nn_calendar_1 NOT NULL); |
Now you know how to rename columns and change column data types.
Dropping columns and constraints
This section shows you how to drop columns and constraints.
Instruction Details →
You drop columns from tables rarely, but the syntax is easy. You would drop the following short_month_name column from the calendar table with this:
SQL> ALTER TABLE calendar 2 DROP COLUMN short_month_name; |
You would encounter a problem dropping a column when the column is involved in a table constraint (a constraint across two or more columns). For example, attempting to drop a middle_name column from the contact table fails when the columns referenced by a multiple-column unique constraint. The statement would look like this:
SQL> ALTER TABLE contact 2 DROP COLUMN middle_name; |
It would raise the following error message:
DROP COLUMN middle_name * ERROR AT line 2: ORA-12991: COLUMN IS referenced IN a multi-COLUMN CONSTRAINT |
Oracle Database 12c and its predecessors disallow you from dropping a column that’s a member of a multiple-column unique constraint. You must drop the constraint before you drop the column and then create a new unique constraint across any remaining columns.
Dropping constraints is easy, because all you need to know is a constraint’s name. The following drops the unique_name constraint from the contact table:
SQL> ALTER TABLE contact 2 DROP CONSTRAINT unique_name; |
Now you know how to drop columns and constraints from tables.
