Creating and updating database objects
In the first section of the chapter, we looked at browsing objects and data in the database. You may, if you are an application developer and working with a completed database design, spend most of your time doing just that. It is more likely though, that you'll need to make changes such as adding or modifying structures and data. SQL Developer provides the dialogs to assist you.
Creating new objects
In the same way that you can write SQL queries to extract information about the objects you have access to, you can write the SQL Data Definition Language (DDL) to create, edit, and modify these objects. Almost all of the object nodes in the Connections navigator provide a context menu to create a new object. To invoke a dialog that creates any new object, select the object in the navigator and invoke the context menu.
Once again, instead of reviewing each of the Create dialogs available in SQL Developer, we'll look at a few of the more common ones, show off some of the features, and then single out a few.
With the exception of Packages and Types, each Create dialog has at least two tabs. One of these is the DDL tab which displays the DDL syntax that will be executed, based on the detail provided on the preceding tabs. The details needed to populate the code in the DDL tab vary considerably, from the more involved trigger dialog to the straightforward synonym dialog.
The tables and views Create dialogs offer a choice of layout. The initial dialog displayed is the same as all of the other create dialogs, offering two tabs. The first of these tabs is for the input required to assemble the DDL to be executed, which is displayed in the second of the two tabs.
The DDL to create tables and views can get quite involved, so you have a second option in these dialogs. Select Advanced on the initial Create Table dialog to switch the display. You can switch back and forth with no loss of detail. While referred to as the advanced dialog, this is arguably the easier dialog to work with, as the Create DDL is broken down into components. Here, you can address all of the aspects of creating the table. Once you have selected Advanced for either tables or views, this layout will be displayed for each new table or view, until you reset the choice. In the Advanced dialog, the DDL is a node in the tree and no longer displays in a separate tab.
Using the previous screenshot displayed; there are various points to consider. The first is that the set of properties displayed varies, depending on the Table Type setting. You can set the Table Type setting after you have added many of the properties. However, it is advisable to set this property first as you are then directed to add type-appropriate details. While using the dialog, you can check whether verifying the DDL is as you expect, by selecting DDL in the tree. It is important to make the Table Type decision when creating the table, as this cannot be modified after the table has been created. For example, the table is either Normal or Temporary, you can't subsequently decide to make a Normal table Temporary. Another dec ision to make as you create the table is about partitioning. Again, once the table is created without partitions, you can't return to it later and add partitions.
You can modify tables after they are created in many ways, and possibly the most common is to add columns. Here too, it is important to remember that columns added to an existing table are added at the end, after the existing columns. The best time to handle column order is on creation. This Create Table dialog allows you to switch the column order. While not attempting to teach best practice here, it is best to keep the Not Null columns up at the top, and the less important columns, which may stay empty for the most part, at the end. To review all of the column detail, it may be useful to switch back to the Create Table grid layout. Using this layout, you can ascertain whether the mandatory columns are at the top of the list, or even verify that you have the data types correct. The grid layout is shown in the following screenshot:
You can add or modify constraints at any point, so it is not critical to do so at the point of creating the table. Certain keys, such as the Primary Key, and possibly even the Foreign Key, may be known and so it is easy enough to add them while in the dialog.
To add any of the constraint types while creating the table you need to select Advanced in the Create Table dialog. Select the appropriate node in the tree, and complete the details as required. We'll review adding a Foreign Key to the projects table example shown earlier. To add a Foreign Key constraint:
- Switch to Advanced in the Create Table dialog.
- Select the Foreign Keys node in the tree.
- Click on Add. This enables the various drop-down lists available in the dialog and pre-populates the fields.
- Select the reference schema. Note you do not need to select a table within the current schema.
- Select the Referenced Table and Referenced Constraint from the lists available.
- Set the associated columns for the Foreign Key.
- You can deselect Enabled to create the constraint, but leave it disabled initially.
For some tables, users define a surrogate key for the Primary Key. As you are not using a real value for the data, you can populate this key using a sequence. In this case, you need to create a sequence and a trigger that fires each time you add a new record. This fetches the next value from the sequence and populates the record. You may have a general sequence that is used for all tables, or you may create a new sequence for each table. Either way, SQL Developer provides assistance when creating a table, for creating the sequence and trigger. Like constraints, this is something that can be done after you have created the table. However, in this case, you need to add the code manually or use another SQL Developer utility, as the option is not available in the table editor once the table has been created.
To add a sequence and trigger to populate a column, select Column Sequences in the tree. Select the required column and decide whether you are going to use an existing sequence or create a new one:
The advanced dialog provides you with panels for creating partitioned tables, setting up storage parameters for LOB columns, and general storage options.
You can set up storage parameters while creating the table using SQL Developer. To add general table storage options, select Table Properties. For LOB specific storage options, select the Lob Parameters node in the tree.
Setting up partition requirements for a table must be done when you create the table. A table is either partitioned or not. This is not something you can switch on or off. SQL Developer provides you with the framework to create Range, Hash, or List partitions. If you elect to partition by Range, you can create further subpartitions. Once defined, you can specify storage and LOB storage properties.
The Create View dialog also provides an Advanced dialog option. When creating views, you have a choice of three approaches:
- Write the SQL Query
- Use the Quick-pick objects
- Build up the query using elements in the dialog
You have a free text box to write the full syntax for the view (as seen in the previous screenshot). There is a Check Syntax button to verify the syntax and a Test Query button that executes the SQL to verify the validity.
For less complex queries, the syntax is parsed and the various other nodes, except the Quick-pick objects, are populated. In the simple example that we have just seen, once you have tested the SQL, the EMPLOYEES table is parsed out into the FROM clause node.
The Quick-pick objects node is useful when you are creating a view that joins a number of tables together. Select the tables based on the joins, and then the columns from the various tables, all in one step.
The first stage of the Quick-pick node is to query the objects available to you. You can either click on the Query button, or select the Auto-Query checkbox. Either way, you need to populate the Available section. Let's assume we want to create a view based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables in the HR schema. We know that an employee works in a department, and that each department is in a location. Proceed as follows:
- Starting with EMPLOYEES, expand the node to expose the Foreign Key, EMP_DEPT_FK.
- Expand the EMP_DEPT_FK to expose the location Foreign Key, DEPT_LOC_FK.
- Expand the LOCATIONS table.
- Now, select a column from each table, and shuttle them over to the Selected section as shown in the following screenshot:
Now, select the From clause node in the tree. Notice that the joins are already listed and created. If you want to modify the joins even more, select each term JOIN in turn, and click on the Edit button. Using the Edit Join dialog (shown in the following screenshot), you can adjust the join syntax used in the view:
As you step down through each of the additional nodes in the tree, you can review the elements in the query already populated from the Quick-pick list and add further elements.
In the SELECT clause, you can start adding items by clicking the + button, which creates an empty field. To populate this, type the detail into the Expression field, provide an alias, and then click Validate to push the new item into the<empty> item above this. Alternatively, expand the Expression Palette, select, and double-click to shuttle the items to the Expression field. If you want to apply a function to the item, select the function for the lower list and, as before, Validate will validate and then populate the selected list. In the following screenshot, we want to add the POSTAL_CODE:
- Click the green + to add a new item to the SELECT List.
- Expand the LOCATIONS node in the Expressions Palette.
- Select POSTAL_CODE and shuttle it to the Expression field on the left-hand side.
- Click on Validate to add the new item.
You can continue working through the view elements in the tree and add further detail, building up the view. Check any final view information and review the DDL. Click on OK to create the view.
The third option starts with the FROM clause, not using Quick-pick objects at all. This provides the same Auto-Query facility and the same tree for expanding. You can elect to shuttle individual tables across to the Selected section, you then need to create the joins. You can expose the tree and then shuttle the tables across as part of the tree, this will automatically create the joins.
Having started with the FROM clause section of the tree, you must then proceed to the SELECT clause and populate each of the items that you require, in the select list as described above.
We'll now consider a few additional database objects, which differ from the others in the tree.
To create new users, select the Other Users node for any privileged user, such as System, or any other user who has the Create User System Privilege.
The Create User dialog (as shown in the following screenshot) has a series of tabs for user details, including Tablespace settings, Roles, and System Privileges. This dialog also has an SQL tab, and when you click on Apply, the results of the execute SQL are displayed. While the CREATE USER statements are not complex, having a dialog that provides a complete listing of roles and privileges available for a user, means that you can quickly create new users.
You only have access to the create dialogs if you have the System Privilege to create the object. To see this in action, expand the Other Users node for the SYSTEM connection, select HR, invoke the context menu, and select Edit User. Under System Privileges, deselect CREATE PROCEDURE. Click on Apply and Close. Return to the top-level Connections navigator and expand your HR connection. Select the Procedures node and invoke the context menu. Note that the option to create a new object is now unavailable. If the user HR was connected when you changed the privilege, you need to disconnect and reconnect to activate the new privilege status (be sure to set the privilege back when you are done).
Editing objects: Putting context menus to work
Each object in the Connections navigator has a context menu associated with it. Each context menu invokes a dialog that supports SQL commands that range from a single, simple command (such as DROP TABLE HR.COUNTRIES) to a collection of basic and complex commands. Throughout the chapter, we have stated that any of the activities we're performing uses a UI as an interface for the underlying SQL command. Certainly, typing the drop table command is faster than selecting the items and invoking the dialog, assuming of course that you know the syntax. In this section, we'll briefly single out a few of these context menus.
Each of the Create dialogs has a matching Edit dialog. Most of the Edit dialogs directly match the object's Create dialog. If you invoke any Edit dialog, the DDL tab or node is initially empty, as it was when you first invoked the Create dialog. As soon as you change any of the properties, the DDL tab or node is populated with the SQL to reflect the changes. For many objects, such as triggers or views, editing the object results in a Create or Replace... command. For other objects, such as Sequences or Tables, editing the objects results in an Alter... command.
Consider the Edit Table dialog as shown in the following screenshot. The dialog is in the form of the advanced Create table dialog. Notice that you can no longer switch the table type, nor create partitions. While you can certainly add or delete columns, you cannot reorder them (unless you drop and recreate them). You'll find that other database rules, such as reducing the column width, are also enforced if the column already contains data.
Each object has an associated context menu, supporting many of the SQL statements associated with the object. For example, constraints can be created, renamed, or dropped, and disabled or enabled. Not only that, but you can also disable all constraints for a table or disable a single constraint. You can disable or enable all related Foreign Keys. These commands are available through a set of context menus of the Tables node:
Some context menus invoke dialogs that go further than creating a single DDL command. They perform a series of actions, which we think of utilities. You have already seen a context menu that performs a number of DDL commands in the Create Table dialog, where you can populate the detail for the columns, the constraints, and add a new sequence and trigger. The resulting DDL tab displays a number of SQL commands, which are executed consecutively. Another dialog that can produce a long list of individual commands is the Create / Edit User dialog.
We'll now review two context menus which invoke dialogs that produce a number of SQL commands. As we review these menus, consider the kinds of SQL activities that you repeat. You can create your own context menus like these using user extensions to capture that activity. This is discussed in more detail in the chapter on Extending SQL Developer.
You may, from time-to-time, work with spreadsheets of data. Some companies or teams have many spreadsheets that are passed around, containing valuable data. As the team grows, or someone realizes the data would be more valuably placed in a database, the detail in the spreadsheet is pulled into the database into a single table. This is no way to do database design, but it probably happens more often than it should. As soon as the data is displayed in a sortable grid, duplicate values become more apparent. The process of normalizing tables, with data in them, involves a few steps. Consider the following set of data (the script to create the BIGEMP table is available in the code bundle available on the Packt web site).
Notice the duplicate DEPARTMENT_NAME values displayed. This detail should be in a separate BIGDEPT table. Normalizing the BIGEMP table means that we need to create a new table for the department's detail. The new table should have a Primary Key that is populated as we move the department name records across. It also needs a Foreign Key that links the tables.
SQL Developer handles all this detail in a single dialog. Select the table, and click on the Column | Normalize item in the context menu. Populate the fields with the details to create the new table. Include, the column that will be moved, the new sequence, and the trigger that fires to populate the Primary Key column. The following screenshot shows the initial dialog:
The SQL tab includes the set of SQL DDL statements wrapped in PL/SQL block:
In the same way as there are multiple options for constraints, there are a variety of context sensitive commands for triggers including enabling, disabling, creating, and dropping. The list of context menu items for a trigger also includes Create (PK From Sequence).
This follows the same concept as described earlier when creating tables. As mentioned, you can create a sequence and trigger to populate the PK value from the sequence, at the time of creating a table. SQL Developer provides you with the context menu to do this after the table has been created. In this situation, the sequence should already exist.
