SQL Workshop’s Object Browser is somewhat misnamed, as it not only allows you to view database objects, but also allows you to create and edit them as well. For now, you’re going to skip your USERS table; you’ll come back to it later in the book. Right now, you’re going to focus on your TICKETS and TICKET DETAILS tables. From this point forward, you’ll be following step-by-step instructions that will be interspersed with figures and discussions about what you’re trying to achieve and why you’re doing it the way you are. So let’s get started:
1. Start by logging into your APEX workspace. You’ll be presented with the workspace’s home page, which, unless you’ve been doing other work in this workspace, will probably look a little sparse.
2. Once you’re at your workspace home page, use the tabbed navigation bar across the top of the page and pull down the SQL Workshop sub-menu by clicking on the arrow on the right side of the tab (see Figure 4–1).
3. Click on the Object Browser option.
Figure 4–1. Navigate to the Object Browser
4. In the Object Browser, click on the Create button in the upper right hand corner and select Table from the drop-down menu.
5. The Create Table Wizard will open. The first screen (Figure 4–2) allows you to name the table and enter the details for each of the columns of the table.
The following wizard will walk you through the steps of creating a table. The first screen allows you to name the table and enter the name and details for each of the columns of the table.
Using the two arrows under the Move column, you can move the columns to what ever order you like. This affects the order in which they are defined and stored in the table.
If you run out of empty rows to enter columns into, you can use the Add Column button to add a new empty column definition row to the form.
Figure 4–2. Defining the table and its columns
Download from Wow! eBook <www.wowebook.com>
6. Enter the details for the TICKET table as indicated in the ERD from the end of Chapter 3 and in Figure 4–2. Then click Next.
The next page (Figure 4–3) lets you choose how you would like the primary key to be populated and which column to use as the primary key. The four options for primary key are fairly self-explanatory, but the two in the middle are probably the most common. You’re starting from scratch and therefore don’t have any existing sequences defined in your database. By selecting “Populate from a new sequence,”
APEX will create a sequence for you and will create a database trigger on the table that will populate the selected Primary Key column with the next value from the sequence, unless the field already has a value.
You’re given the chance to name the sequence within this step as well. In this instance, you’ll use the default name given.
Figure 4–3. Defining the table’s primary key
7. Select the Populated from a new sequence radio button and after the screen changes, select TICKET_ID (NUMBER) for primary key. Click Next.
8. You’re not going to create any foreign keys in this table just yet, so simply leave the defaults and click Next.
The Constraints screen in Figure 4–4 allows you to add either Unique or Check constraints to the table definition. Add a constraint by defining the constraint in the Add Constraints region and clicking the Add button to add it to the list. Below the Add Constraints region are two help regions. Clicking on the gray arrow to the left of the region title will expand the help and show the columns you defined in the table and examples of how to code various check constraints.
Once you’ve clicked the Add button, the definition of the constraint is added to the list of constraints at the top of the page. You can define as many constraints on a given table as necessary.
Once you’re done, simply continue on with the wizard.
9. You’re not going to create any Unique or Check constraints here, so stick with the defaults and click Next.
Figure 4–4. The Constraints definition step
The final step of the Create Table wizard shows you the code that will be executed and allows you to make any changes. For instance, the Table wizard doesn’t allow you to add column level comments, but you could easily add them by editing the text displayed in the SQL region. You can see the code for the USERS table in Figure 4–5.
Figure 4–5. Review the Create Table wizards SQL
10. Review the text in the SQL region presented by the Create Table Wizard. Click Create to complete the wizard.
Once you’ve successfully completed the wizard, you’ll be taken back to the Object Browser and the definition of the TICKETS table will be displayed. Take a moment to examine the definition of the table.
You should see all the columns that you defined listed. If you click on the Constraints tab across the top of the definition region, you’ll see a number of different constraints including the primary key constraint on TICKET_ID.
In the upper left hand corner of the object browser, there is a select list that defines the object type being browsed. Use this select list and choose Sequences. You’ll see that APEX created a sequence called TICKETS_SEQ that will be used fill the TICKET_ID.
Once again, use the Object Type select list and chose Triggers. You’ll see a trigger named
BI_TICKETS (BI stands for “before insert”). Clicking on the Code tab above the trigger details will show the code for the trigger that is using the TICKETS_SEQ sequence to fill the TICKET_ID if it’s null. You should see code similar to the following:
create or replace trigger "BI_TICKETS"
before insert on "TICKETS"
for each row begin
if :NEW."TICKET_ID" is null then
select "TICKETS_SEQ".nextval into :NEW."TICKET_ID" from dual;
end if;
end;
Now that you have your Tickets table defined, let’s go back and create your TICKET_DETAILS tables. This time you’ll be creating a foreign key to the TICKETS table. You’ll be creating the foreign key to the TICKETS table as a CASCADE DELETE. This means that if you delete the ticket, the ticket details will automatically be deleted as well.
11. Start the Create Table wizard using the Create button.
12. Enter the table name and column definitions based on the ERD and Figure 4–6, and click Next.
The next set of steps is purposefully a bit more vague than the previous ones. You should be used to using the Create Table wizard by now, but if you need a refresher, just look at the previous steps.
Figure 4–6. Defining the TICKET_DETAILS table
13. Choose to populate the primary key from a new sequence, select TICKET_DETAILS_ID(NUMBER) as the primary key, and click Next.
14. Add a foreign key between the TICKET_ID in the TICKET_DETAILS table and the TICKET_ID in the TICKETS TABLE. Make sure that the Delete action is set to Cascade Delete. Your screen should look similar to that in Figure 4–7.
Figure 4–7. Defining Cascade Delete foreign key for the TICKET_ID
Figure 4–8. Foreign keys as defined in the table wizard.
15. Once the foreign key is defined, click Next (see Figure 4–8).
16. No constraints are required for this table. Click Next.
17. Review the SQL and click Create to complete the wizard.