Changing a primary key is a two-step process: dropping the primary key and recreating it. This is illustrated in Listing 4.33. Suppose you had originally created the Course table with a primary key consisting only of the Course ID. After some thought, you realize that different departments will want to have courses with the same number. Therefore, you change the primary key to include both the Course ID and the Department ID.

Listing 4.33. Changing a primary key.

SQL> alter table Course add
  2  (primary key (Course_ID));

Table altered.

SQL> alter table Course drop primary key;

Table altered.

SQL> alter table Course add
  2  (primary key (Course_ID, Department_ID));

Table altered.

Script for Creating the Sample Database

Now that you have covered the basic techniques used to create tables, you're ready for a look at the script for creating our sample database which is shown in Listing 4.34.

The first portion of the script is used to drop all the tables. If you try to drop a table that is referenced by another table's foreign key, Oracle will return an error code.

The only exception to this is if you use the CASCADE clause in the DROP TABLE statement. Therefore, if you don't use the CASCADE clause, it is essential that the tables be dropped in an order such that the table being dropped is not referenced by a foreign key in any existing tables.

Listing 4.34. Creating the sample database tables.

drop table Student_Schedule;

drop table Student;

drop table Class;

drop table Class_Location;

drop table Course;

drop table Schedule_Type_Details;

drop table Schedule_Type;

drop table Instructor;

drop table Department;

create table Department
(Department_ID     Varchar2(20),
Department_Name   Varchar2(25)
 constraint NN_Department_Name NOT NULL,
 constraint PK_Department
 primary key (Department_ID));

create table Student
(Student_ID     Varchar2(20),
 Last_Name      Varchar2(25)
 constraint NN_Student_Last_Name NOT NULL,
 First_Name     Varchar2(25)
 constraint NN_Student_First_Name NOT NULL,
 MI             Varchar2(1),
 Year           Varchar2(25),
 Street_Address Varchar2(25),
 City           Varchar2(25),
 State          Varchar2(2),
 Zipcode        Varchar2(9),
 Telephone      Varchar2(10),
 Fax            Varchar2(10),
 Email          Varchar2(100),
 constraint PK_Student
 primary key (Student_ID));

create table Course
(Course_ID       Varchar2(5),
 Department_ID   Varchar2(20)
 constraint NN_Course_Department_ID NOT NULL,
 Title           Varchar2(60)
 constraint NN_Course_Title NOT NULL,
 Description     Varchar2(2000),
 Units           Number,
 Additional_Fees Number,
 Constraint PK_Course

                                           continues

Listing 4.34. continued

 Primary key (Course_ID, Department_ID),
 Constraint FK_Course_Department_ID
 Foreign key (Department_ID) references Department (Department_ID));

create table Instructor
(Instructor_ID     varchar2(20),
 Department_ID     varchar2(20)
 constraint NN_Instructor_Dept_ID NOT NULL,
 Last_Name         varchar2(25)
 constraint NN_Instructor_Last_Name NOT NULL,
 First_Name        varchar2(25),
 MI                varchar2(1),
 Position          varchar2(25) constraint CK_Instructor_Position
 check (Position in (`ASSISTANT PROFESSOR', `ASSOCIATE PROFESSOR',
                     `PROFESSOR')),
 Telephone         varchar2(10),
 Fax               varchar2(10),
 Email             varchar2(100),
 constraint PK_Instructor Primary Key (Instructor_ID),
 constraint FK_Instructor_Department_ID
 Foreign Key (Department_ID) references Department (Department_ID));

create table Schedule_Type
(Schedule_ID          varchar2(20),
 Schedule_Description varchar2(2000),
 constraint PK_Schedule_Type primary key (Schedule_ID));

create table Schedule_Type_Details
(Schedule_ID          varchar2(20),
 Day                  number
 constraint CK_Schedule_Det_Day check (Day between 1 and 7),
 Starting_Time        date,
 Duration             number,
 constraint PK_Schedule_Type_Details primary key (Schedule_ID, Day),
 constraint FK_Schedule_Det_ID
 foreign key (Schedule_ID) references Schedule_Type (Schedule_ID));

create table Class_Location
(Class_Building     varchar2(25),
 Class_Room         varchar2(25),
 Seating_Capacity   number,
 constraint PK_Class_Location primary key (Class_Building, Class_Room));
create table Class
(Class_ID           varchar2(20),
 Schedule_ID        varchar2(20),
 Class_Building     varchar2(25),
 Class_Room         varchar2(25),
 Course_ID          varchar2(5),
 Department_ID      varchar2(20),
 Instructor_ID      varchar2(20),
 Semester           varchar2(6),
 School_Year        date,
  constraint PK_Class primary key (Class_ID),

 constraint FK_Class_Location
 foreign key (Class_Building, Class_Room) references
   Class_Location (Class_Building, Class_Room),
 constraint FK_Class_Schedule_ID
 foreign key (Schedule_ID) references Schedule_Type (Schedule_ID),
 constraint FK_Class_Course_ID
 foreign key (Course_ID, Department_ID) references
    Course (Course_ID, Department_ID),
 constraint FK_Class_Dept_ID
 foreign key (Department_ID) references Department (Department_ID),
 constraint FK_Class_Instructor_ID
 foreign key (Instructor_ID) references Instructor (Instructor_ID));

create table Student_Schedule
(Student_ID          varchar2(20),
 Class_ID            varchar2(20),
 Grade               varchar2(2)
 constraint CK_Grade check (Grade in (`A', `A+', `A-', `B', `B+', `B-',
                               `C', `C+', `C-', `D', `D+', `D-',
                               `F', `F+', `F-')),
 Date_Grade_Assigned date,
 constraint PK_Student_Schedule
 primary key (Student_ID, Class_ID),
 constraint FK_Student_Schedule_Student_ID
 foreign key (Student_ID) references Student (Student_ID),
 constraint FK_Student_Schedule_Class
 foreign key (Class_ID) references Class (Class_ID));

Summary

Keep the following ideas in mind when implementing your logical design:

• Use the CREATE TABLE statement via SQL*Plus or SQL Worksheet for greater control over the statement options or to process a script.
• Use the ALTER TABLE statement to modify an existing table.
• Define default values for columns where appropriate.
• Every table should have a primary key.
• Use foreign keys to enforce referential integrity.
• You can define a table's primary key by using the CREATE TABLE or ALTER TABLE statement.
• You can drop a table's primary key with the ALTER_TABLE statement.
• You can disable or enable a constraint by using the ALTER TABLE statement.
• You can drop a constraint with the ALTER TABLE statement.
• A table index can be defined for up to 16 columns.
• A table index may be unique or nonunique.

• Don't create unique indexes with the CREATE UNIQUE INDEX statement; instead, declare primary key and unique constraints and let Oracle create the indexes automatically.
• Use a CHECK constraint to restrict a column's value to a fixed or calculated range.

What Comes Next?

On Day 5, "Introduction to Structured Query Language (SQL)," you learn more about four very important SQL statements: SELECT, INSERT, UPDATE, and DELETE. These statements are used to retrieve and manipulate information in an Oracle database.

Q&A

Q What is the ideal number of indexes for a table?

A The answer is—it depends. At the very least, an index will be created automatically by the declaration of the primary key. Beyond that, you probably will want to add a nonunique index for each foreign key. You also may want to add a nonunique index for each column that is frequently part of a query condition and has a wide distribution of values. However, you should rarely create any additional indexes for tables with a small number of rows—for example, one hundred or less.

Q What is the advantage of declaring a column CHECK constraint? Can't the constraint be enforced in an application such as Oracle Forms or Visual Basic?

A Yes, it is possible and sometimes desirable to enforce a constraint also at the application level. However, you should consider the declarative constraints for a table and its columns to be the first "line of defense" for data integrity. Because a variety of tools are used in the client/server environment, you want to be certain that no one can assign invalid values to a column, regardless of the tool being used.

Q How do you drop or rename a column in a table?

A Oracle does not allow you to rename or drop a column directly. If you want to rename or drop a column, you have two choices: you can drop the table and recreate it, or you can create a new table based on a subset of the columns in the existing table. If you decide to use the latter approach, remember that the new table will not possess any constraints other than NOT NULL constraints.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercise before you continue with tomorrow's lesson.