This chapter is about another subset of SQL: Data Definition Language (DDL). DDL consists of SQL statements used to create, modify, and discard database objects.
If you're involved in developing many database applications, you should definitely consider using a Windows-based database design tool such as Oracle's Designer/2000, Logic Works's ERwin, or System Architect. These products enable you to graphically define a logical model and to generate the correct SQL statements for creating a database.
Even if you're using a database design tool, you should acquire a working knowledge of the Oracle toolset. Personal Oracle7 gives you two ways to create tables, indexes, and other database objects. The first method is to use SQL*Plus. As you saw in Chapter 7, "Accessing Personal Oracle7 with SQL*Plus," SQL*Plus is an interactive tool that you can use to submit all SQL statements.
The other alternative is to use a graphical tool such as the Navigator or Object Manager. If you're using Personal Oracle7 for Windows 95, take advantage of the Navigator's intuitive interface for creating and modifying tables. If you're using Personal Oracle7 for Windows 3.11, you can use Object Manager to perform common database tasks. Unlike SQL*Plus, neither of these tools is command oriented. Both the Navigator and Object Manager package your inputs and submit them to the Oracle7 database engine. These tools are ideal for users who aren't familiar with SQL and don't want to learn the language. Although both products have limitations, the Navigator and Object Manager offer a straightforward interface for viewing and modifying database tables.
To create a table with the Navigator, you'll need to access the Table folder by double-clicking the Local Database icon. If you want to see the existing tables in the Local Database, double-click the Table folder. (See Figure 8.1.)
Viewing the tables in the Local Database. To create a new table with the Navigator, select the Table folder and right-click. The next window (see Figure 8.2) offers you two choices for creating the new table:
Choosing a method for creating the table. In the following example you use the Table Wizard to create the Product table for the repair store.
The Table Wizard displays several pages to help you create a table. Which pages you fill out depends on the table design. To begin, Page 1 of the Table Wizard asks for the name of the new table and its owner. Type PRODUCT for the table name and select FRAYED_WIRES from the drop-down list of Oracle users. (See Figure 8.3.) Click Next to display Page 2 of the Table Wizard.
Using the Table Wizard to specify the name and owner of the new table. Page 2 of the Table Wizard provides a form for entering the definition of each column. Enter the definition of the Product_ID column and click New. (See Figure 8.4.)
Using the Table Wizard to specify the Product_ID column. The next step is to define the Manufacturer_ID column. (See Figure 8.5.) You can use the up and down arrow to the right of the Size input box to increase or decrease a column's width. *
Using the Table Wizard to specify the Manufacturer_ID column. After adding the Description column, you can create the Date_of_Manufacture column, which is a DATE column. Use the drop-down list in the Column Type input box to select the DATE type. (See Figure 8.6.)
Using the Table Wizard to specify the Date_of_Manufacture column. Create the Initial_Retail_Value column and specify a value for Scale. (See Figure 8.7.)
Using the Table Wizard to specify the Initial_Retail_Value column. You can use the VCR-style buttons to navigate between the column definitions. When you have finished specifying each of the new table's columns, click Next to proceed to Page 3 of the Table Wizard. Page 3 allows you to specify whether a column is mandatory--;the column value cannot be null--; and whether the column value is unique. (See Figure 8.8.)
Using the Table Wizard to specify the null and unique characteristics of the Product_ID column. When you have finished specifying the null and unique attributes for each column, click Next. The Table Wizard prompts you for the table owner's password (see Figure 8.9) and uses the password to assemble a list of the tables that are accessible to the table's owner.
The Table Wizard prompts for the table owner's password. Page 4 of the Table Wizard prompts you to specify the foreign keys for the table. This Table Wizard page also lets you specify a check condition for a column--;see Chapter 14, "Defining Table and Column Constraints," for further coverage. For example, the Manufacturer_ID column is a foreign key for the Manufacturer table. (See Figure 8.10.) When you have finished specifying all the foreign keys and check conditions for the table, click Next.
Using the Table Wizard to specify the foreign key for the Manufacturer_ID column. Page 5 of the Table Wizard prompts you to specify the table's primary key. For example, the primary key for the Product table is composed of two columns: Product_ID and Manufacturer_ID. (See Figure 8.11.) Click Next to continue to Page 6.
Using the Table Wizard to specify the primary key for the Product table. Page 6 of the Table Wizard displays the order of the columns. (See Figure 8.12.) You may change this order by selecting a column and clicking Up or Down. When the order of the columns is acceptable to you, click Next to go to the last page of the Table Wizard.
Using the Table Wizard to specify the order of the columns. Page 7 of the Table Wizard asks you if you want to enter data. (See Figure 8.13.)
Table Wizard asks if you want to enter data into the new table. The default answer is Yes. Click Finish to create the table and enter data into the newly created table. (See Figure 8.14.)
When you choose to create the new table manually, the Navigator displays a form in which you specify the name of the new table, the table owner, and the definition of each column. Figure 8.15 illustrates how to create the Product table.
Manually creating a table with Navigator. For example, for a column whose value cannot be null, make sure that the Can be Null? field is not checked. To specify the table's primary key, click the Primary Key field for each column; the number that appears in the field will increment for each additional column that is part of the primary key. (See Figure 8.16.)
Specifying a table's primary key with the Navigator. To specify a foreign key, enter owner.table_name(column_name) in the Foreign key field. For example, to specify the foreign key for the Manufacturer_ID column, you would type FRAYED_WIRES.MANUFACTURER(MANUFACTURER_ID). (See Figure 8.17.)
Specifying a foreign key for a table with the Navigator. When you have finished filling out the form, click OK to create the table.
The Object Manager icon resides in the Personal Oracle7 Windows group. You invoke Object Manager by double-clicking the Object Manager icon. Object Manager prompts you for an Oracle username and password for connecting to the database. (See Figure 8.18.) Stick to the current example and type FRAYED_WIRES for the username and HELMHOLTZ for the password; then click the OK button.
Entering the username and password in Object Manager. After you've successfully connected to the Personal Oracle7 database with Object Manager, click the Type drop-down list and select Table as the database object. Object Manager then retrieves the existing tables owned by the Frayed_Wires Oracle account. (See Figure 8.19.)
Object Manager displays a list of existing tables. Click the Create button to invoke the Create New Object pop-up window. From the group of radio buttons that represent the type of database object to be created (see Figure 8.20.), select Table and click OK.
Choosing the type of database object to create. The next pop-up window, titled Table Management, functions much like a spreadsheet program and is initially empty. (See Figure 8.21.) The initial width of the Column_Name column is fairly narrow. You can increase its width by moving the pointer to the line that separates the Column_Name and Type columns, holding the mouse button down, and moving the pointer to the right.
Empty spreadsheet displayed by Table Management. The next element to create is the Manufacturer table. For each column, follow this procedure (see Figure 8.22):
Defining a table's columns with Object Manager. In Figure 8.23, some of the spreadsheet columns have been resized to display the Primary Key, Foreign Key, and Check Condition columns.
Additional columns displayed in the Table Management spreadsheet. After you've defined all the columns for the Manufacturer table, click the Save button. Object Manager displays the New Table Name window. Type Manufacturer for the table name and click OK. (See Figure 8.24.)
Supplying a new table name to Object Manager. At this point, Object Manager has packaged the information entered on the spreadsheet, assembled a CREATE TABLE statement (which is hidden from the user), and submitted the statement to the Oracle7 database engine. If the table is created successfully, Object Manager displays a success message. The Table Management window then displays the new table name, as shown in Figure 8.25.
You've seen how easy it is to create a table with Personal Oracle7. However, it's crucial that you take the time to study the optimal design for your application's database.
A facet of relational database theory comes into play in this discussion. Normalization theory is the study of relations (tables), attributes (columns), and the dependency of attributes upon one another. The goals of normalization include Minimizing redundant data
Avoiding update anomalies
Reducing inconsistent data
Designing data structures for easier maintenance
Because we're talking theory here, you need to understand some essential terminology.
Table 8.1 lists these terms in three categories: theoretician, analyst, and developer.
For example, a professor of computer science may write about relation XYZ,
whereas an application developer may refer to the same item as table XYZ.
As you read about databases, whether the document is academic or commercially oriented,
and as you read this chapter, you'll see these terms used interchangeably. You can
use whichever terms you prefer, as long as you use them appropriately and understand
what they represent.
| Table 8.1. Orientation of database terminology. | ||
| Theoretician | Analyst | Developer |
| Relation | Entity | Table |
| Attribute | Attribute | Column |
| Tuple | Row | Row/Record |
Normalization theory describes the desired arrangements of tables and columns as Normal Forms. This discussion considers the First, Second, and Third Normal Forms, which are often cited as 1NF, 2NF, and 3NF. Although these terms sound theoretical and abstract, they are actually quite intuitive. Other Normal Forms--;Boyce-Codd, Fourth, and Fifth--;address more complex normalization issues. Those topics are beyond the scope of this book.
An entity (table) is in First Normal Form if all of its attributes are atomic. By atomic I mean that each attribute consists of a single fact about the entity. In addition, an entity should not have attributes that represent repeating values. To understand the implications of First Normal Form, consider the consequences of not adhering to the first normalization rule.
Suppose that you used a single table to store all customer information. Suppose that the Customer table consolidates Street_Address, City, State, and Zip_Code into a single Address column that can contain up to 80 characters. Consolidating several columns into a single column takes away your ability to control the formatting of Address. As a result, the format of a customer's address could vary from record to record.
SQL> select Customer_ID, Last_Name, First_Name, Address
2 from Customer;
CUSTOMER_ID LAST_NAME FIRST_NAME ADDRESS
----------- ------------- ------------ ----------------------------------------
1001 Johnson Frank 1001 S. Elm Ave. Detroit, Michigan 01234
1002 Richards Mary 321 N. Main St. Jackson, MI 01235
1003 Soule Harold 919 Allen Circle, Flint, Mich. 01919
1004 Golden Marie 222 N. Michigan Ave., Chicago, IL 01933
What happens when you want to assemble a list of customers who live in Michigan? The following query won't retrieve the complete list:
SELECT Customer_ID, Last_Name, First_Name FROM Customer WHERE upper(Address) LIKE '%MICHIGAN%';
The next query returns a record that doesn't belong to the set you are looking for.
SELECT Customer_ID, Last_Name, First_Name FROM Customer WHERE upper(Address) LIKE '%MICH%';
To design a SELECT statement that retrieves the desired information, you must look at the entire set of data and account for all exceptions. For the non-1NF Customer table, this query returns the customers who live in Michigan:
SELECT Customer_ID, Last_Name, First_Name FROM Customer WHERE upper(Address) LIKE '%MICHIGAN%' or upper(address) LIKE '%,MI %' or upper(address) LIKE '%,MICH %';
For an entity to be in Second Normal Form, all of its columns must depend on the primary key only. Put simply, this rule means that a table must not contain extraneous information. For example, the purpose of the Depot_Estimate table is to store estimates received from the repair depot. The primary key consists of Repair_ID, Item_Number, and Depot_ID; all three columns are necessary to uniquely define a row in the table. If the Depot_Estimate table also contained a column for storing the repair depot's address, the table would not be in 2NF; the repair depot's address depends on the Depot_ID only, not on the entire primary key.
To be in Third Normal Form, a table's columns must be entirely dependent on the primary key. The key word in that last sentence is entirely. Each column in the table must be dependent on the entire primary key, not just a portion of it.
Again, I'll use the Depot_Estimate table to illustrate this rule. Suppose you added a column named Product_ID to this table. This additional column would contain the product ID for the item for which the estimate has been prepared. But you are already storing this product ID in the Repair_Item table. You can obtain this information by joining these tables by the Repair_ID and Item_Number columns (which will be discussed in further detail in Chapter 15, "More Sophisticated Queries"). The value of Product_ID depends upon the Repair_ID and the Item_Number only--;not upon the Depot_ID. So we can say that Product_ID is dependent on part of the primary key, but it is not wholly dependent on the primary key.
This concept is sometimes referred to as the derived column. In the previous example, Product_ID can be derived from Repair_ID and Item_Number. According to relational theory, a table should not contain any derived columns. In practice, tables frequently contain derived columns.
If you read articles about relational database technology, you will encounter the "great debate." Relational purists say that all tables must be in at least 3NF, although practitioners argue that to achieve acceptable performance, you must denormalize a database--;in other words, reduce the database design from 3NF to 2NF. My position is somewhere in the middle. Here are my recommendations:
Enough about theory--;now you are ready to use SQL to create and alter tables.
Because of its many options and clauses, the SQL statement CREATE TABLE can be rather complex. A simplified version of its syntax is
CREATE TABLE table_name ( column_name1 datatype [NOT NULL], ... column_nameN datatype [NOT NULL]);
where table_name is the name for the table; column_name1 through column_nameN are valid column names; and datatype is a valid Oracle datatype specification.
You can invoke the CREATE TABLE statement directly from SQL*Plus (and SQL*DBA). Object Manager packages the user's entries and indirectly invokes the CREATE TABLE statement. This section focuses on the use of SQL*Plus in creating tables.
Oracle imposes several restrictions on table names:
On the other hand, because Oracle is not case sensitive, you can use uppercase and lowercase characters in your table names.
As a rule, your table names should be descriptive. Resist the urge to use excessive abbreviations when naming tables and other database objects. Many application development tools, such as PowerBuilder or Oracle Forms 4.5, provide a point-and-click interface for selecting a table. Because no typing is involved, a long table name is just as easy to select as a short table name.
Here are some considerations for naming columns:
This section starts with a simple example that constructs a CREATE TABLE statement to create the Depot_Estimate table.
SQL> CREATE TABLE Depot_Estimate 2 (Item_Number NUMBER(2) NOT NULL, 3 Depot_ID NUMBER(4) NOT NULL, 4 Repair_ID NUMBER(6) NOT NULL, 5 Labor_Cost DECIMAL(7,2), 6 Parts_Cost DECIMAL(7,2), 7 Total_Cost DECIMAL(7,2), 8 Estimated_Date_for_Completion DATE, 9 Technician VARCHAR2(30), 10 Telephone_Number VARCHAR2(10), 11 Created_Date DATE, 12 Created_By VARCHAR2(30), 13 Modified_Date DATE, 14 Modified_By VARCHAR2(30) 15 ); Table created.
Some aspects of the statement's syntax deserve special mention:
As an option, you can explicitly specify that a column is optional by specifying NULL after the datatype specification, as shown in the following code segment:
SQL> CREATE TABLE Depot_Estimate 2 (Item_Number NUMBER(2) NOT NULL, 3 Depot_ID NUMBER(4) NOT NULL, 4 Repair_ID NUMBER(6) NOT NULL, 5 Labor_Cost DECIMAL(7,2) NULL, 6 Parts_Cost DECIMAL(7,2) NULL, 7 Total_Cost DECIMAL(7,2) NULL, 8 Estimated_Date_for_Completion DATE NULL, 9 Technician VARCHAR2(30) NULL, 10 Telephone_Number VARCHAR2(10) NULL, 11 Created_Date DATE NULL, 12 Created_By VARCHAR2(30) NULL, 13 Modified_Date DATE NULL, 14 Modified_By VARCHAR2(30) NULL 15 ); Table created.
As I mentioned, Oracle's implementation of SQL is not case sensitive (which isn't true of all vendors). Here's an example:
SQL> CREATE TABLE depot_ESTIMATE 2 (ITEM_NUMBER NUMBER(2) NOT NULL, 3 DEPOT_id NUMBER(4) NOT NULL, 4 repair_ID NUMBER(6) NOT NULL, 5 Labor_Cost DECIMAL(7,2), 6 Parts_Cost DECIMAL(7,2), 7 Total_Cost DECIMAL(7,2), 8 Estimated_Date_for_Completion DATE, 9 Technician VARCHAR2(30), 10 Telephone_Number VARCHAR2(10), 11 Created_Date DATE, 12 Created_By VARCHAR2(30), 13 Modified_Date DATE, 14 MODIFIED_BY VARCHAR2(30) 15 ); Table created.
Although Oracle SQL is not case sensitive, you should still be consistent in your references to table and column names.
A table's primary key is the set of columns that uniquely identify each row in the table. Once again the CREATE TABLE syntax is
CREATE TABLE table_name ( column_name1 datatype [NOT NULL], ... column_nameN datatype [NOT NULL], [Primary key (column_nameA, column_nameB, ... column_nameX)]);
where table_name is the name for the table; column_name1 through column_nameN are valid column names; column_nameA through column_nameX are the table's columns that compose the primary key; and datatype is a valid Oracle datatype specification.
Although the primary key clause of the CREATE TABLE statement is an essential concept in relational database theory, in practice its use is optional. Using the Depot_Estimate table again, here's how the primary key is declared:
SQL> CREATE TABLE Depot_Estimate 2 (Item_Number NUMBER(2) NOT NULL, 3 Depot_ID NUMBER(4) NOT NULL, 4 Repair_ID NUMBER(6) NOT NULL, 5 Labor_Cost DECIMAL(7,2), 6 Parts_Cost DECIMAL(7,2), 7 Total_Cost DECIMAL(7,2), 8 Estimated_Date_for_Completion DATE, 9 Technician VARCHAR2(30), 10 Telephone_Number VARCHAR2(10), 11 Created_Date DATE, 12 Created_By VARCHAR2(30), 13 Modified_Date DATE, 14 Modified_By VARCHAR2(30), 15 Primary Key (Repair_ID, Depot_ID, Item_Number)); Table created.
Primary keys are subject to several restrictions. First, a column that is part of the primary key cannot be null. Second, a column that is defined as LONG or LONG RAW cannot be part of the primary key. Third, the maximum number of columns in the primary key is 16. You can circumvent this last restriction through the use of a surrogate key--;an artificial value that can be guaranteed to uniquely identify all rows in a table.
Now that you know how to specify the primary key in the CREATE TABLE statement, you can declare your foreign keys. The following CREATE TABLE syntax includes primary and foreign key declarations:
CREATE TABLE table_name ( column_specification1, ... column_specificationN, [Constraint constraint_name Foreign key (column_nameF1,...column_nameFN) references referenced_table (column_nameP1,...column_namePN),] [Constraint constraint_name Primary key (column_nameA, column_nameB, ... column_nameX)]);
Here is an explanation of the variables: table_name is the name for the table.
column_specification1 through column_specificationN are valid column specifications (described below in detail).
constraint_name is the constraint name that you want to assign to a primary or foreign key.
referenced_table is the name of the table referenced by the foreign key declaration.
column_nameF1 through column_nameFN are the columns that compose the foreign key.
column_nameP1 through column_namePN are the columns that compose the primary key in referenced_table.
column_nameA through column_nameX are the table's columns that compose the primary key.
datatype is a valid Oracle datatype specification.
The syntax for a column_specification is
column_name datatype [DEFAULT default_value][CONSTRAINT constraint_name] [NULL]|[NOT NULL]|[UNIQUE]|CHECK (condition)where column_name is a valid Oracle column name.
datatype is a valid Oracle datatype specification.
default_value is a legal default value assigned to the column on an insert.
constraint_name is a legal constraint name to be assigned to the constraint--;NOT NULL, UNIQUE, or CHECK.
condition is a valid Oracle Boolean condition that must be true for a value to be assigned to a column.
For the Depot_Estimate table, you actually need to declare two foreign keys:
Therefore, you need to add two foreign keys to the CREATE TABLE statement.
SQL> CREATE TABLE Depot_Estimate 2 (Item_Number SMALLINT NOT NULL, 3 Depot_ID NUMBER(4) NOT NULL, 4 Repair_ID NUMBER(6) NOT NULL, 5 Labor_Cost DECIMAL(7,2), 6 Parts_Cost DECIMAL(7,2), 7 Total_Cost DECIMAL(7,2), 8 Estimated_Date_for_Completion DATE, 9 Technician VARCHAR2(30), 10 Telephone_Number VARCHAR2(10), 11 Created_Date DATE, 12 Created_By VARCHAR2(30), 13 Modified_Date DATE, 14 Modified_By VARCHAR2(30), 15 Primary key (Repair_ID, Item_Number, Depot_ID), 16 Constraint fk_repair_item 17 Foreign key (Repair_ID, Item_Number) references 18 Repair_Item (Repair_ID, Item_Number), 19 Constraint fk_depot Foreign key (Depot_ID) 20 references Depot (Depot_ID) 21 ); Table created.
TipI recommend that you use constraint names in a CHECK clause or when declaring foreign keys, even though they are optional. If you don't explicitly declare a constraint name, Oracle automatically generates a constraint and assigns a rather cryptic name to it. If you want to drop the foreign key, you'll have to look up the Oracle-generated constraint name. You can save yourself some grief by declaring a constraint in the first place.
The columns in the referenced table must actually compose the primary or unique key of the referenced table. If they don't, Oracle will not create the foreign key. In the following example, you drop the primary key from the Depot table. When Oracle attempts to create the foreign key, it determines that Depot_ID is not defined as the primary key for the Depot table and the CREATE TABLE statement fails.
SQL> alter table Depot drop primary key;
Table altered.
SQL> CREATE TABLE Depot_Estimate
2 (Item_Number SMALLINT NOT NULL,
3 Depot_ID NUMBER(4) NOT NULL,
4 Repair_ID NUMBER(6) NOT NULL,
5 Labor_Cost DECIMAL(7,2),
6 Parts_Cost DECIMAL(7,2),
7 Total_Cost DECIMAL(7,2),
8 Estimated_Date_for_Completion DATE,
9 Technician VARCHAR2(30),
10 Telephone_Number VARCHAR2(10),
11 Created_Date DATE,
12 Created_By VARCHAR2(30),
13 Modified_Date DATE,
14 Modified_By VARCHAR2(30),
15 Primary key (Repair_ID, Item_Number, Depot_ID),
16 Constraint fk_repair_item
17 Foreign key (Repair_ID, Item_Number) references
18 Repair_Item (Repair_ID, Item_Number),
19 Constraint fk_depot Foreign key (Depot_ID)
20 references Depot (Depot_ID)
21 );
Constraint fk_depot Foreign key (Depot_ID) references Depot (Depot_ID)
*
ERROR at line 19:
ORA-02270: no matching unique or primary key for this column-list
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 returns an error code.
Here is a simple example in which you modify the definition of the Customer table so that the table accepts only California, Illinois, or Michigan for the customer's state.
SQL> CREATE TABLE Customer
2 (Customer_ID NUMBER(4) NOT NULL,
3 Last_Name VARCHAR2(30),
4 First_Name VARCHAR2(30),
5 Salutation VARCHAR2(5),
6 Street_Address VARCHAR2(60),
7 City VARCHAR2(30),
8 State VARCHAR2(2)
9 constraint Customer_State_CK check (state in ('CA','IL','MI')),
10 Zipcode VARCHAR2(9),
11 Work_Telephone_Number VARCHAR2(10),
12 Home_Telephone_Number VARCHAR2(10),
13 Fax_Number VARCHAR2(10),
14 Earliest_Time_to_Call DATE,
15 Latest_Time_to_Call DATE,
16 Local_Time_Zone VARCHAR2(3),
17 Created_Date DATE,
18 Created_By VARCHAR2(30),
19 Modified_Date DATE,
20 Modified_By VARCHAR2(30)
21 );
Table created.
If you now attempt to insert a customer whose address isn't in California, Illinois, or Michigan, Oracle returns an error code indicating that a check constraint has been violated.
SQL> insert into Customer 2 (Customer_ID, Last_Name, First_Name, State) 3 values 4 (6002, 'Moore', 'Les', 'HA'); insert into Customer * ERROR at line 1: ORA-02290: check constraint (FRAYED_WIRES.SYS_C00419) violated
For more information about constraints, please refer to Chapter 14.
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 you wanted to be sure that if the labor cost in the Depot Estimate table wasn't specified, its default value would be $50. Here is how you would modify the CREATE TABLE statement to achieve this result:
SQL> CREATE TABLE Depot_Estimate 2 (Item_Number SMALLINT NOT NULL, 3 Depot_ID NUMBER(4) NOT NULL, 4 Repair_ID NUMBER(6) NOT NULL, 5 Labor_Cost DECIMAL(7,2) 6 DEFAULT 50.0, 7 Parts_Cost DECIMAL(7,2), 8 Total_Cost DECIMAL(7,2), 9 Estimated_Date_for_Completion DATE, 10 Technician VARCHAR2(30), 11 Telephone_Number VARCHAR2(10), 12 Created_Date DATE, 13 Created_By VARCHAR2(30), 14 Modified_Date DATE, 15 Modified_By VARCHAR2(30) 16 ); Table created.
Now that the table has been defined, you can try inserting a row without specifying a value for Labor_Cost. The SELECT statement returns a single row with Labor_Cost set to the default value of 50.0.
SQL> insert into Depot_Estimate
2 (Repair_ID, Item_Number, Depot_ID)
3 values
4 (2002, 1, 1001);
1 row created.
SQL> select Repair_ID, Item_Number, Depot_ID, Labor_Cost
2 from Depot_Estimate;
REPAIR_ID ITEM_NUMBER DEPOT_ID LABOR_COST
--------- ----------- --------- ----------
2002 1 1001 50
If you're using Personal Oracle7 for Windows 95, you can use the Navigator to modify a table. To modify a table--;change its structure in some way--;you must first select the table and right-click Design. The Design Table window will appear. (See Figure 8.26.)
Modifying a table with the Navigator. Oracle allows you to make certain changes to a table's structure. For example, you can
However, you cannot change the name of an existing column or delete a column. Also, if a column contains a value, you cannot decrease its length, disallow null values, or change its datatype. If you attempt any of these modifications, you will see an error message when you click OK. (See Figure 8.27.)
The Navigator returns an error during an attempt to decrease the length of a column Also, you may be prevented from changing the primary key for the table if other tables with foreign keys point to the table you want to change. (See Chapter 14 for more details.) Aside from these restrictions, you may modify a table's structure as required.
If you're using Personal Oracle7 for Windows 3.11, you can modify a table with Object Manager. Suppose you want to modify Depot_Estimate by adding another column to record a priority code. To begin, double-click the Object Manager icon and log in to Oracle as FRAYED_WIRES. Next, select the table object to retrieve the tables owned by FRAYED_WIRES. (See Figure 8.28.)
Retrieving the list of tables to modify. Select the Depot_Estimate table and click the Show button. A pop-up window displays the current definition of the Depot_Estimate table. (See Figure 8.29.)
Pop-up window displays the definition of Depot_Estimate. After the final column, specify Priority_Code as the column name and NUMBER as the datatype. Set the width of Priority_Code to 2. (See Figure 8.30.)
Adding another column to a table. You can also use Object Manager to define the primary key for a table. In the Primary Key column of the spreadsheet, click the Repair_ID, Item_Number, and Depot_ID columns. As you click the mouse button, Object Manager places a number in the cell that reflects the number of columns in the primary key. (See Figure 8.31.)
Specifying the primary key with Object Manager. The Table Management spreadsheet also provides a Default column in which you can specify a default value for one of the table's columns. Enter 50 in this column for Labor_Cost. (See Figure 8.32.)
Specifying a default value for a column. Object Manager also enables you to specify a Check Condition for a column. The Check Condition column is the right-most column on the Table Management spreadsheet. In Figure 8.33 the Check Condition column specifies that the Total_Cost column must be greater than or equal to 0.
Specifying a Check Condition for a column. Click the Save button to actually save the changes that you have made to the table. Confirm the table changes in the pop-up window. (See Figure 8.34.)
Confirming the table changes. Finally, Oracle displays a message indicating that the table changes were made without error. (See Figure 8.35.)
You can use the ALTER TABLE statement to modify a table's definition. This statement changes the structure of a table, not its contents. You can use the ALTER TABLE statement to
The ALTER TABLE statement has four basic forms.
ALTER TABLE table_name
ADD (column_specification | table_constraint ,...
column_specification | table_constraint);
or
ALTER TABLE table_name
MODIFY (column_specification | table_constraint ,...
column_specification | table_constraint);
or
ALTER TABLE table_name DROP PRIMARY KEY;
or
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
The first form of the statement adds a column, the primary key, or a foreign key to a table.
The second form of the statement modifies an existing column. Among other things, you can increase a column's width or transform a column from mandatory to optional.
The third and fourth forms of the ALTER TABLE statement drop a table's primary key and other constraints.
In the next few pages, I demonstrate the use of this statement with several examples.
Although you can freely change a column from mandatory to optional, you should think about why the change is necessary. Is an attribute really optional, or are you working with a test data set that doesn't represent realistic application data?
For example, the current definition for demo_table is
SQL> desc demo_table Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) DESCRIPTION VARCHAR2(40) CURRENT_VALUE NOT NULL NUMBER
To change the column from mandatory to optional, you issue this command:
SQL> alter table demo_table modify (current_value number null); Table altered. SQL> desc demo_table Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) DESCRIPTION VARCHAR2(40) CURRENT_VALUE NUMBER
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.
Here's how Oracle responds if you attempt to make the current_value column 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
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.
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.
You can use the ALTER TABLE statement to increase the width of a character column. Suppose the current definition of a table is this:
SQL> desc demo_table Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) DESCRIPTION VARCHAR2(40) CURRENT_VALUE NOT NULL NUMBER
To increase the width of the description column from 40 to 50 characters, you enter this command:
SQL> alter table demo_table modify (description varchar2(50)); Table altered. SQL> desc demo_table Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) DESCRIPTION VARCHAR2(50) CURRENT_VALUE NOT NULL NUMBER
Increasing the width of numeric columns works in the same way:
SQL> desc demo_table Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) DESCRIPTION VARCHAR2(50) CURRENT_VALUE NOT NULL NUMBER MAX_READINGS NUMBER(4)
If you want to increase the width of max_readings, you can use this statement:
SQL> alter table demo_table 2 modify 3 (max_readings number(6)); Table altered.
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.
You can also use the ALTER TABLE statement to decrease a column's width. First, examine this sample table.
SQL> desc repair_item Name Null? Type ------------------------------- -------- ---- ITEM_NUMBER NOT NULL NUMBER REPAIR_ID NOT NULL NUMBER(6) PRODUCT_ID VARCHAR2(12) MANUFACTURER_ID VARCHAR2(6) CONDITION_CODE VARCHAR2(3) SERIAL_NUMBER CHAR(18) PURCHASE_DATE DATE WARRANTY_EXPIRATION_DATE DATE ESTIMATED_COST NUMBER(7,2) ESTIMATED_COMPLETION_DATE DATE CUSTOMER_AUTHORIZATION_STATUS VARCHAR2(10) CUSTOMER_AUTHORIZATION_DATE DATE CREATED_DATE NOT NULL DATE CREATED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE DATE MODIFIED_BY VARCHAR2(30)
Recall that Jim Helmholtz uses a two-character code to keep track of the customer's authorization. Therefore, you decide to "do the right thing" by decreasing the width of Customer_Authorization_Status from 10 characters to 2 characters.
SQL> alter table repair_item 2 modify 3 (customer_authorization_status varchar2(2)); (customer_authorization_status varchar2(2)) * ERROR at line 3: ORA-01441: column to be modified must be empty to decrease column length
As you might have suspected, Oracle does not allow you to decrease a column's width if the column has values. Stop to examine the contents of the column.
SQL> select customer_authorization_status 2 from repair_item; CUSTOMER_A ---------- ON HOLD AUTHORIZED AUTHORIZED ON HOLD AUTHORIZED ON HOLD 6 rows selected.
Even if you update the Repair_Item table so that the Customer_Authorization_Status contains only two characters, Oracle won't allow you to reduce the width of the column, as you can see:
SQL> update repair_item 2 set customer_authorization_status = 3 substr(customer_authorization_status,1,2); 6 rows updated. SQL> commit; Commit complete. SQL> select customer_authorization_status 2 from repair_item; CUSTOMER_A ---------- ON AU AU ON AU ON 6 rows selected. SQL> alter table repair_item 2 modify 3 (customer_authorization_status varchar2(2)); (customer_authorization_status varchar2(2)) * ERROR at line 3: ORA-01441: column to be modified must be empty to decrease column length
However, if you update the table by setting the Customer_Authorization_Status to NULL, Oracle will permit you to reduce the column's width.
SQL> update repair_item 2 set customer_authorization_status = null; 6 rows updated. SQL> commit; Commit complete. SQL> alter table repair_item 2 modify 3 (customer_authorization_status varchar2(2)); Table altered.
During the database design phase, a developer typically experiments with an application's database structures to determine their accuracy and suitability by populating tables with sample data. If a table requires a change--;for example, an additional column--;altering the table is much more convenient than dropping the table and recreating it with its new definition. However, sometimes the necessary change requires modification of the sample data.
Changing a table's primary key without disturbing the rest of a database design is often difficult. The reason is simple: Tables are usually related to one another through the declaration of foreign keys. A foreign key depends upon the existence of a primary key in another table. Therefore, if you change the primary key, the change can ripple throughout the entire database. In fact, Oracle prevents that from happening.
Changing a primary key is a two-step process: dropping the primary key and recreating it. Suppose you had originally created on the Customer table a primary key consisting of the customer's last and first names. After some thought, you realize that you have made a mistake and decide to use Customer_ID as the primary key.
SQL> alter table Customer add 2 (primary key (Last_Name, First_Name)); Table altered. SQL> alter table Customer drop primary key; Table altered. SQL> alter table Customer add 2 (primary key (Customer_ID)); Table altered.
First of all, you cannot drop a primary key that is referenced by a foreign key. In the sample database, the Repair_Header table contains the Customer_ID column. A foreign key is declared for the Customer_ID column that references the Customer table and the Customer_ID column. If you attempt to drop the primary key of the Customer table, here's what occurs:
SQL> alter table Customer drop primary key; alter table Customer drop primary key * ERROR at line 1: ORA-02273: this unique/primary key is referenced by some foreign keys
The procedure for changing a table's foreign key is slightly different from the process of changing a primary key. You can't modify an existing foreign key--;you must drop the constraint associated with the foreign key and recreate it. Here's how you drop a foreign key on the Repair_Item table:
SQL> CREATE TABLE Repair_Item 2 (Item_Number SMALLINT NOT NULL, 3 Repair_ID NUMBER(6) NOT NULL, 4 Product_ID VARCHAR2(12), 5 Manufacturer_ID VARCHAR2(6), 6 Condition_Code VARCHAR2(3), 7 Serial_Number CHAR(18), 8 Purchase_Date DATE, 9 Warranty_Expiration_Date DATE, 10 Estimated_Cost DECIMAL(7,2), 11 Estimated_Completion_Date DATE, 12 Customer_Authorization_Status CHAR(18), 13 Customer_Authorization_Date CHAR(18), 14 Created_Date DATE NOT NULL, 15 Created_By VARCHAR2(30) NOT NULL, 16 Modified_Date DATE, 17 Modified_By VARCHAR2(30), 18 Constraint fk_product 19 foreign key (Product_ID, Manufacturer_ID) references 20 Product (Product_ID, Manufacturer_ID)); Table created. SQL> alter table Repair_Item drop constraint fk_product; Table altered.
Here is how you would add a foreign key to the Repair_Item table:
SQL> alter table Repair_Item add 2 (constraint new_fk_product 3 foreign key (Product_ID, Manufacturer_ID) references 4 Product (Product_ID, Manufacturer_ID)); Table altered.
Keep the following ideas in mind when designing your Personal Oracle7 database: