Listing 4.10. Changing a column from optional to mandatory.

SQL> describe demo_table

Name                            Null?    Type
 ------------------------------ -------- ----
 RECORD_NO                      NOT NULL NUMBER(38)
 DESCRIPTION                             VARCHAR2(40)
 CURRENT_VALUE                  NOT NULL NUMBER

SQL> alter table demo_table modify (current_value number null);

Table altered.

SQL> describe demo_table

Name                            Null?    Type
 ------------------------------ -------- ----
 RECORD_NO                      NOT NULL NUMBER(38)
 DESCRIPTION                             VARCHAR2(40)
 CURRENT_VALUE                           NUMBER

Changing a Column Definition from NULL to NOT NULL

If a table is empty, you can define a column to be NOT NULL. However, if a table isn't empty, you can't change a column to NOT NULL unless every row in the table has a value for that particular column. Listing 4.11 shows how Oracle will respond if you attempt to make the current_value column mandatory. However, if you ensure that current_value has a non-null value for each row in the table, you'll be able to set the current_value to NOT NULL.

Listing 4.11. Attempt to change a column to be mandatory.

SQL> alter table demo_table modify (current_value number not null);
alter table demo_table modify (current_value number not null)
                               *
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL

SQL> update demo_table
  2  set current_value = record_no + 100;

4 rows updated.

SQL> select current_value from demo_table;

CURRENT_VALUE
-------------
          121
          122

          123
          124

SQL> commit;

Commit complete.

SQL> alter table demo_table modify (current_value number not null);

Table altered.

Initially, all the rows in the table have a null value for the CURRENT_VALUE column; as a result, the column cannot be modified to be mandatory. To modify the column so that it is mandatory, all the rows must have a value for CURRENT_VALUE. Arbitrarily, you update all the rows in the table so that the CURRENT_VALUE column is assigned the value of the RECORD_NO column plus 100. Then, the column can be modified so that it is defined as NOT NULL.

Increasing a Column's Width

You can use the ALTER TABLE statement to increase the width of a character column. The example shown in Listing 4.12 demonstrates its use. Initially, the width of the Description column is 40 characters. To increase the width of the Description column from 40 to 50 characters, the column definition is modified with the ALTER TABLE statement.

Listing 4.12. Increasing a column's width.

SQL> describe demo_table
 Name                           Null?    Type
 ------------------------------ -------- ----
 RECORD_NO                      NOT NULL NUMBER(38)
 DESCRIPTION                             VARCHAR2(40)
 CURRENT_VALUE                  NOT NULL NUMBER

SQL> alter table demo_table modify (description varchar2(50));

Table altered.

SQL> describe demo_table
 Name                           Null?    Type
 ------------------------------ -------- ----
 RECORD_NO                      NOT NULL NUMBER(38)
 DESCRIPTION                             VARCHAR2(50)
 CURRENT_VALUE                  NOT NULL NUMBER

Decreasing a Column's Width

In the course of designing a database application, you may find that you erred in sizing a column: you specified a larger size than a column actually requires. Initially, you might not think that having a larger-than-required column is a problem because it doesn't prevent the column from accommodating the data. But it is a problem because it indicates that your data model is inaccurate. If you want to "do the right thing," you need to reduce the size of the column so that the column definition mirrors what is allowed for the data.

You also can use the ALTER TABLE statement to decrease a column's width. For example, suppose you want to use a single character to indicate the student's current year—1 for freshman, 2 for sophomore, and so on. However, the column Year is currently defined as varchar2(25). Listing 4.13 shows what happens when you try to do this.

Listing 4.13. Attempting to decrease a column's width.

SQL> alter table Student
  2  modify
  3  (Year varchar2(1));
(Year varchar2(1))
 *
ERROR at line 3:
ORA-01441: column to be modified must be empty to decrease column length

As you might have suspected, Oracle will not allow you to decrease a column's width if the column has values. Even if you update the Student table so that all the values for Year are no more than five characters, Oracle still will not allow you to reduce the width of the column, as you can see in Listing 4.14. You cannot reduce the width of the column until it is set to NULL.

Listing 4.14. Another attempt to decrease a column's width.

SQL> update Student
  2  set Year = substr(Year,1,5);

31 rows updated.

SQL> alter table Student modify
  2  (Year varchar2(5));
(Year varchar2(5))
 *
ERROR at line 2:
ORA-01441: column to be modified must be empty to decrease column length

SQL> update Student
  2  set Year = NULL;

31 rows updated.

SQL> alter table Student modify
  2  (Year varchar2(5));

Table altered.

Of course, once the column is set to NULL, all the values are lost. In reality, you'd make a copy of the table as it is so that you could reload the column values; you'll learn more about this on Day 6, "Using SQL to Modify Data."

Using a Primary Key

You should be sure to define a primary key for every table in your database for the following reasons:

• A primary key prevents duplicate rows from being inserted into a table. Depending on the circumstances, duplicate rows can be a nuisance or can cause an application to crash. For example, you wouldn't want two departments to have the same department ID; declaring the Department_ID as the primary key will prevent this from occurring.
• A table must have a primary key if it is going to be referenced by a foreign key in another table.

Defining a Primary Key During Table Creation

You can define a table's primary key in the CREATE TABLE statement with the following syntax:

CREATE TABLE table-name
(column-definition1,
...
column-definitionN,
[CONSTRAINT constraint-name] PRIMARY KEY (column1,...,columnN))

The variables are defined as follows:

table-name is a valid Oracle table name.
column-definition1 through column-definitionN are legal column declarations.
constraint-name is a constraint name that is assigned to the primary key constraint.
column1 through columnN are the columns that compose the primary key.

Listing 4.15 illustrates how the primary key prevents duplicate rows from being inserted into the table. The primary key is enforced, even though the first INSERT transaction hasn't been committed.