Prepare the database
Deleting data
Now that you know about DML, you could use the delete statement to remove all of the rows from the tables in your species database. Because you want to remove all the data, you will not need a where clause; however, you will need to put your statements into the correct order to avoid errors due to the foreign key constraints.
Alternatively, you could just run your schema creation script from last week. Because it drops and recreates the tables, it is equivalent to deleting all the data.
Creating sequences
This week you will be inserting data for the tables country, region, genus and species. Of these, only genus does
not have a synthetic key. The other three tables therefore need a sequence to generate new key values. Refer to the
notes from last week to work out the syntax for each create sequence statement and add them to your schema maintenance
script. Don't forget that to make your script re-runable by including drop sequence statements at the start.
Test your sequences by executing a statement like the following two or three times:
1 2 | |
Verify that you can query the most recent value generated by altering the statement above to replace nextval with currval.
Creating triggers
Again, refer to last week's notes for the syntax to use to create a trigger. Using the example provided, you will only need to change the names of the objects for it to work.
Test your sequences by writing an insert statement for each of your tables which inserts values for all the columns except the key. After running the insert statement, you should be able to query the new row and see that the key values have been inserted correctly.
Cleaning up
Before inserting any real data, you will need to delete any test rows that you have inserted. However, you do not need to recreate the sequences. Although you have used some of the values in your tests, the values themselves carry no intrinsic meaning. As long as the values are unique, it does not matter that they do not start from 1.