Schema maintenance scripts

As you are developing a database schema, there may be occasions on which you have to change your mind about a table or column definition. For example, you may have underestimated the column size for a primary key field. In that case, you would need to update the table definition - possibly using an alter table statement. However, if that column is referenced in a foreign key in another table, you will need to update that column definition as well. Similarly, if you decide to change the name of a column, you will need to change it wherever it is referenced. One change may have consequences like this which can cause errors to creep into your structures.

Rather than making each change individually and directly on the database, keeping a database maintenance script allows you to make changes quickly and easily. A maintenance script is a series of SQL statements in a text file which recreates the database every time it is run. First it removes any existing database objects (see below), and then it goes on to create the required objects and finally to insert the data. Having all of your maintenance statements in a single file means that you can make changes easily by using the standard global search and replace functions of a text editor.

Note that the coursework asks you to hand in a schema maintenance script.

Starting from scratch

Of course, you can write your file from scratch using a text editor if you prefer. To use this approach, save your work in a file called species.sql

Schema maintenance script

The simplest way to create the first version of your schema maintenance script is to use the SQL Developer New Table... dialog for each table in turn, and then to export the table definitions. If you have not already done so, create all the other tables in the SPECIES schema following the process described previously. If you use the table export facility there are two things to bear in mind. First, SQL Developer will include more detail than you need, and you should remove that extra detail when you edit your script. Second, you should only use the export facility to create the first version of each CREATE TABLE statement. After that, you should make any modifications to your schema maintenance script rather than through the SQL Developer GUI.

The next few steps describe how to export the table definitions from your schema using the two tables we created earlier as an example.

In the connections explorer, highlight all of the tables in the SPECIES schema as shown below, right-click one of them and select Export... from the pop-up menu.

The export wizard lets you include a lot of information about the way the tables are stored and gives you options for how to format the generated DDL. We are not interested in most of these because they make the SQL statements much more complicated. In the wizard, deselect the options labelled (1) in the graphic below, but make sure that the Drops options is selected (2). Choose an appropriate location for the generated file and give it the name species.sql (3).

Accept the defaults in the remaining steps of the wizard. Once the file has been generated, it is also loaded into the SQL Developer display so you can examine the contents. Notice that the script starts with a set of drop table statements. This is to ensure that you can run the script over again without manually dropping the database objects first. However, if you try to run the script now (choosing the correct database connection when prompted) you will see the following error:

The reason for this error is obvious if you think back to how you created the foreign key constraint between the two tables. Recall that a foreign key must correspond to the primary key in another table. The DBMS therefore prevents the removal of a table if its primary key is referenced by a foreign key.

The solution is equally straightforward: simply drop the tables in the opposite order! Once the table containing the foreign key constraint has been dropped, you will be able to drop the parent table with no problems. Edit the file in SQL Developer to change the order in which tables are dropped until your script runs without errors.

Tools which generate code rarely do it perfectly. The problem described here is a clear oversight in the way SQL Developer works. In this case it would be quite trivial to work out the correct order in which to drop the tables. However, it is not always so simple. Consider the following two tables:

Because there are two parallel relationships between the tables with one foreign key in each, it is not possible to order the tables to avoid error ORA-2449. The only solution in this case is to drop the constraints first (using alter table statements) before dropping the tables. SQL Developer generates alter table statements to create the constraints; there is no reason why it should not do the same to drop them. This is a useful reminder that you should not rely completely on the available tools to do all the work for you. You still have to understand what is required in order to correct problems manually when things go wrong.