We interact with many databases in our daily lives to get information. For example, a phone book is a database of names and phone numbers, and an email list is a database of customer names and email addresses. A database is a collection of individual named objects (such as tables) to organize data. In APEX you create data-centric web applications that are powered by Oracle database. In this tutorial, I will walk you through creating database objects interactively for the sales application you just created. The application will use five tables to store information: DEMO_STATES, DEMO_CUSTOMERS, DEMO_PRODUCT_INFO, DEMO_ORDER, and DEMO_ORDER_ITEMS. These tables will be generated interactively (that is, without writing any SQL code) with the help of a built-in wizard. In addition to these tables, some other database objects, such as sequences and triggers, will be generated automatically to handle data in these tables.
Execute the following steps to create the first table named DEMO_CUSTOMERS for the sales web application. This table will be used to store the profiles of customers.
1. Click
the SQL Workshop menu (A).
2. Select the Object Browser option (B) from the menu,
which is used to review and maintain database objects (such as tables, sequences, views, functions,
triggers, and so on).
3. In the Object Browser page, select the Tables option (C) from the select list.
This action will show a list of existing tables in the left pane if there are
any.
4. Click
the Create menu (D),
and select Table (E) from the menu
list to create a new table. This will invoke a wizard named Create Table,
discussed next.
5. On the first wizard page
you provide a name for the new table and information about its columns – name,
type, precision, scale, and not null. Enter DEMO_CUSTOMERS for the table name (A). Enter CUSTOMER_ID in the first Column Name (B). Select NUMBER (C) for the Type of this column.
Place a checkmark for Not Null (D). This information specifies that the first
column in the table named CUSTOMER_ID is a numeric column that will hold the ids of
customers. By placing a checkmark in the Not Null option, we specified that it
is a mandatory column and must have some value. Input information of
other table columns as indicated in the screenshot below. The values in the
Scale column specify the number of characters each column will hold. Use the
Add Column button (E) to add more rows to the form. After providing the column
information, click Next (F) to proceed to the next wizard step.
6. The next wizard screen
titled Primary Key collects information about the primary key of
this table, which is a column in a table that uniquely identifies each record
and prevents duplicates. The primary key (CUSTOMER_ID) in the customers' table
will be populated automatically with the help of a database object named
Sequence. From the Primary Key options, select Populated from a new sequence (A). As you click this option, three
additional fields pop up on your screen. Accept DEMO_CUSTOMERS_PK (B) for the Primary
Key Constraint Name.
You can specify any other name if you wish to. This is the name of your primary
key constraint to uniquely identify each row/record in the customers' table. For
Primary Key, select CUSTOMER_ID (C) from
the adjacent list. This is the column that will act as the primary key to
uniquely identify each record in the table. Accept the name of the default
Sequence Name or enter any other name. A
Sequence is a database object which generates unique
integer values automatically in the background. Here, it will generate unique
primary keys for each customer’s record, and these values will be stored in the
CUSTOMER_ID column. Press the Next button thrice skipping Foreign Key and Constraints
wizard screens. On the final Confirm
screen, click the Create Table
button. The table will be created and its definitions will appear on your
screen. Click the SQL tab to see the
auto-generated SQL statements for this table. The trigger (BI_DEMO_CUSTOMERS)
created for this table will be responsible to auto-generate ids of customers
with the help of the DEMO_CUSTOMERS_SEQ sequence object. You can view both these
objects using the drop-down list in the left pane (under the Object Browser label).
7. Repeat steps 4 to create another table named DEMO_PRODUCT_INFO (A). As the name
suggests, this table will store information about products that will be sold to
customers. Enter column definitions for this table as depicted in the following
screenshot. Besides number and varchar2 column types, this table is using the BLOB (Binary
Large Objects) type (B), which is an Oracle data type that
can hold up to 4 GB of data. BLOBs are handy for storing digitized information,
such as images, audio, and video. This type can also be
used to store document files like PDF,
MS Word, MS Excel, MS PowerPoint, and CSV to name a few. We are also using a TIMESTAMP type (C) to store the date when a product
image is updated. Click Next to
proceed.
8. Once again select Populated from a new sequence (A) on
the Primary Key screen. Accept the default values for Primary Key Constraint Name and Sequence
Name. Select the PRODUCT_ID
column (B) for Primary Key. This is the column that will uniquely identify each
product in the table. Click Next. Skip
the Foreign Key and Constraints
wizard screens by clicking the Next button. On the final screen, click the Create Table button.
9. After creating the
parent tables, let’s create two more tables (DEMO_ORDERS and DEMO_ORDER_ITEMS)
to store customers’ order information. These two tables will have a
master/detail relationship. The DEMO_ORDERS table will act as the master table
to store master information, like order id, customer id, order date, and more.
The DEMO_ORDER_ITEMS will be the child table for the DEMO_ORDERS table and it
will store line item information, such as product id, unit price, and quantity.
So, let’s first create the master table. Again, execute steps 1 to initiate the Create Table wizard. Fill in the
information for this table as indicated in the following screenshot, and click
Next.
10. As usual, select the Populated from a new sequence option to
automatically populate the primary keys for this table as well. Accept the
default values for Primary Key Constraint Name and Sequence
Name. Select the ORDER_ID column
(A) for Primary Key. Click Next to proceed to the Foreign Key screen, where you will create a relationship
between DEMO_CUSTOMERS and DEMO_ORDERS table.
11. The following wizard
screen collects information about Foreign Key. A foreign key establishes a relationship between a column or
columns in one table and a primary or unique key in another table. Here, you
are establishing a relationship between the DEMO_ORDERS and DEMO_CUSTOMERS
tables. Accept the default name (DEMO_ORDERS_FK
- A) for the foreign key constraint name. The default Disallow Delete option (B) will block the deletion of rows from
the customers' table when they are utilized in the orders master table. From the
left pane in the Select Key Column(s)
section, move the CUSTOMER_ID column
to the right pane (C) using the single right-arrow icon (>). This action
specifies that the CUSTOMER_ID column in this table is a foreign key and has a reference in some other table. Click
the icon next to the References Table
(D), and pick the DEMO_CUSTOMERS
table. All columns from this table will appear in the Referenced Column(s) left pane. In the Referenced Column(s) section, move the CUSTOMER_ID column to the right pane (E). Here you are telling APEX
that this is the column in the customers' table that will be referenced by the CUSTOMER_ID
column in the orders master table. Now the two tables have a relationship based on the CUSTOMER_ID column. Click the Add button (F). The details of the FK constraint (G) will
appear on the page. Press the Next
button twice, and then click the Create
Table button on the Confirm
screen to create the orders master table.
12. Create the orders child
table (DEMO_ORDER_ITEMS) of your application, as illustrated in the following
figure. This table will be used to store line item information of each order
placed by customers. Click the Next button, after providing the column
information.
13. Select the ORDER_ITEM_ID column as the Primary Key column for this table, and click Next.
14. The DEMO_ORDER_ITEMS has
two foreign key references: ORDER_ID and PRODUCT_ID. So, you
will create two foreign key constraints on the Foreign Keys page. The first one is illustrated in the following
figure in which you relate this table to its parent (DEMO_ORDERS). For this relationship,
you selected the Cascade Delete option (A), which simultaneously removes both
parent and child records from the two tables when you delete an order. After
selecting the ORDER_ID columns from the two tables, click the Add button (B) to create the foreign
key constraint. The next step will be executed on the same page to create
another foreign key reference.
15. In this foreign key constraint you are creating a relationship
between the DEMO_ORDER_ITEMS table and the DEMO_PRODUCT_INFO table using the PRODUCT_ID column, which exists in both tables. Enter DEMO_ORDER_ITEMS_PRODUCT_ID_FK
(A) for the name of this foreign key. Select Disallow Delete for the delete option and select tables and
columns as depicted in the following figure. After that, click the Add button (B). A new foreign key
constraint (C) will be added just under the previous one. This constraint will
prevent the deletion of those products that are utilized in customers’ orders.
Click Next twice to skip the Constraint wizard screen. Click the Create Table button on the final screen.
16. Create the final table (DEMO_STATES), as illustrated in the following figure. This table will store state information and it will be associated with the Customers module to keep each customer's state. Click the Next button, after providing the column information.
17. On the Primary Key screen, select the default No Primary Key option because this table is not going to have any primary key. Click Next.
18. Skip the remaining two wizard screens by clicking Next twice and creating the table.
Here is the summary of the whole exercise you just carried out.
For Visual Instruction Watch This Video
