Listing 4.6. Unable to declare a foreign key because there is no associated primary key.

SQL> alter table Department drop primary key;

Table altered.

SQL> create table Course
  2  (Course_ID       Varchar2(5),
  3   Department_ID   Varchar2(20) NOT NULL,
  4   Title           Varchar2(60) NOT NULL,
  5   Description     Varchar2(2000),
  6   Units           Number,
  7   Additional_Fees Number,
  8   Constraint PK_Course
  9   Primary key (Course_ID, Department_ID),
 10   Constraint FK_Course_Department_ID
 11   Foreign key (Department_ID) references Department (Department_ID));

Foreign key (Department_ID) references Department (Department_ID))
                                                    *
ERROR at line 11:
ORA-02270: no matching unique or primary key for this column-list

Constraining a Column's Value with the CHECK
Clause

A powerful feature of SQL is the capability to specify rudimentary data validation for a column during table creation. SQL accomplishes this task with an optional CHECK clause, which can be specified for each column. The CHECK clause is a Boolean condition that is either TRUE or FALSE. If the condition evaluates to TRUE, the column value is accepted by Oracle; if the condition evaluates to FALSE, Oracle will return an error code.

Listing 4.7 presents an example in which you will modify the definition of the Instructor table so that the table allows an instructor's position to be one of three values—ASSISTANT PROFESSOR, ASSOCIATE PROFESSOR, or PROFESSOR.

Listing 4.7. Declaring a check constraint on a column.

SQL> create table Instructor
  2  (Instructor_ID     varchar2(20),
  3   Department_ID     varchar2(20)  NOT NULL,
  4   Last_Name         varchar2(25)  NOT NULL,
  5   First_Name        varchar2(25),
  6   MI                varchar2(1),
  7   Position          varchar2(25) constraint CK_Instructor
  8   check (Position in (`ASSISTANT PROFESSOR', `ASSOCIATE PROFESSOR',
  9                       `PROFESSOR')),
 10   Telephone         varchar2(10),

 11   Fax               varchar2(10),
 12   Email             varchar2(100),
 13   constraint PK_Instructor Primary Key (Instructor_ID),
 14   constraint FK_Instructor_Instructor_ID
 15   Foreign Key (Department_ID) references Department (Department_ID));

Table created.

If you attempt to add an instructor to the table whose position is not one of the three legal values, Oracle returns an error code indicating that a check constraint has been violated, as shown in Listing 4.8.

Listing 4.8. CHECK constraint is enforced.

SQL> insert into Instructor
  2  (Instructor_ID, Department_ID, Last_Name, First_Name, Position)
  3  values
  4  (`C2222', `PSYCH', `MENCKEN', `H.', `CURMUDGEON');

insert into Instructor
*
ERROR at line 1:
ORA-02290: check constraint (TYO2.CK_INSTRUCTOR) violated

Because CURMUDGEON is not a valid instructor position, the attempt to insert the row fails. This is an example of how a CHECK constraint enforces data integrity.

Establishing a Default Value for a Column

By using the DEFAULT clause when defining a column, you can establish a default value for that column. This default value is used for a column whenever a row is inserted into the table without specifying the column in the INSERT statement.

For example, suppose that most students live in the city of Springfield. As a result, you want to be sure that if a student is added to the Student table and his or her city isn't specified, its default value will be SPRINGFIELD. Listing 4.9 illustrates how you would modify the CREATE TABLE statement to achieve this result. After the default value has been established, a row that is inserted into the Student table without specifying a value for City will be set to a default value of SPRINGFIELD.

Listing 4.9. Specifying a default value for a column.

SQL> create table Student
  2  (Student_ID     Varchar2(20),
  3   Last_Name      Varchar2(25)  NOT NULL,

                                            continues

Listing 4.9. continued

  4   First_Name     Varchar2(25)  NOT NULL,
  5   MI             Varchar2(1),
  6   Year           Varchar2(25),
  7   Street_Address Varchar2(25),
  8   City           Varchar2(25) Default `SPRINGFIELD',
  9   State          Varchar2(2),
 10   Zipcode        Varchar2(9),
 11   Telephone      Varchar2(10),
 12   Fax            Varchar2(10),
 13   Email          Varchar2(100),
 14   Constraint PK_Student Primary Key (Student_ID));

Table created.

SQL> insert into Student
  2  (Student_ID, Last_Name, First_Name, Year)
  3  values
  4  (`109850', `CARSON', `RICARDO', `JUNIOR');

1 row created.

SQL> select Student_ID, Last_Name, City
  2  from Student
  3  where
  4  Student_ID = `109850';

STUDENT_ID           LAST_NAME                CITY
-------------------- ------------------------ ------------------------
109850               CARSON                   SPRINGFIELD

Using ALTER TABLE to Modify the Table
Definition

At times, you'll find it necessary to modify a table's definition. The ALTER TABLE statement serves this purpose. This statement changes the structure of a table, not its contents. Using ALTER TABLE, the changes you can make to a table include the following:

• Adding a new column to an existing table
• Increasing or decreasing the width of an existing column
• Changing an existing column from mandatory to optional or vice versa
• Specifying a default value for an existing column
• Specifying other constraints for an existing column

Here are the four basic forms of the ALTER TABLE statement:

ALTER TABLE table_name
ADD (column_specification | constraint ,...
     column_specification | constraint);
ALTER TABLE table_name
MODIFY (column_specification | constraint ,...
        column_specification | constraint);
ALTER TABLE table_name
DROP PRIMARY KEY;
ALTER TABLE table_name
DROP CONSTRAINT constraint;

The variables are defined as follows:

table_name is the name for the table.
column_specification is a valid specification for a column (column name and datatype).
constraint is a column or table constraint.

The first form of the statement is used for adding a column, the primary key, or a foreign key to a table.

The second form of the statement is used to modify an existing column. Among other things, you can increase a column's width or transform it from mandatory to optional.

The third and fourth forms of the ALTER TABLE statement are used for dropping a table's primary key and other constraints.

In the next few pages, I demonstrate the use of this statement with several examples.

Changing a Column Definition from NOT NULL to NULL

Although you can freely change a column from mandatory to optional, you should think about why the change is necessary. Is this attribute really optional, or are you working with a test data set that isn't representative of realistic application data?

Listing 4.10 displays a table named demo_table. The ALTER TABLE statement is used to change the definition of the Record_No column to be mandatory.