In this fifth part of a seven-part series on using and administering Oracle Database XE, you will learn how to create a table and use constraints. This article is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Creating a Table
To create a table you use the CREATE TABLE command. At a minimum, you must specify the column names and their datatypes. You can optionally specify default values and constraints. Alternatively, you can add these later. In this example, you want to create a table for the orders placed on your e-commerce Web page powered by PHP, of course:
Notice that we specify the tablespacePHP_APPSexplicitly. It’s optional, and if you want all of your tables to reside in the default tablespaceUSERS, you can leave off theTABLESPACEclause. To see the structure of the table you have just created, you can use theDESCRIBEcommand either in the SQL Commands Web interface or at the Run SQL Command Line prompt accessible from the start menu in Windows:
describe customer_order
-------------------------------------------- Name Null? Type ------------------ ----- --------------- ORDER_ID NUMBER CUSTOMER_ID NUMBER ORDER_DATE DATE ORDER_SHIP_DATE DATE ITEM_QTY_NUM NUMBER SHIP_NOTES VARCHAR2(1000) --------------------------------------------
The column in the describe output labeledNull?has a value ofNOT NULLfor table columns that cannot containNULLvalues. Most of the columns in this table should always be provided. We will show you how to require input values for specified columns, and many others, throughout the rest of this chapter.
Of course, you can also use the Oracle Database XE home page to view this table’s characteristics by navigating to the Object Browser from the Oracle Database XE home page. In Figure 30-2, you can see the structure of the tableCUSTOMER_ORDERand the other operations you can perform on the table.
Figure 30-2.Viewing a table’s structure using the Object Browser
