Creating a related table

When creating a table that has a child-parent relationship with another table, the main difference in the process is that you need to define a foreign key constraint. In this exercise, you will add a second table to your SPECIES schema which represents the next level of the animal classification taxonomy, and which is therefore related to genus. The description of the table is:

species (species_id, genus, species, common_name )

Notice that species has a synthetic key: you will also need to take this into account when you are thinking about the table structure.

Using the same process as before to select appropriate column definitions, you should end up with something like that shown below.

The synthetic primary key in this case is numeric (1). With numbers in Oracle, it is not necessary to specify a column size (2) because Oracle uses a default value. However, if you were constructing a table where you expected there to be a large number of rows (> 100,000 for example) you would probably pay more attention to the storage overhead for numerical data. In large tables, proportionally large overheads in data storage space can accumulate to the point where they start to adversely affect performance and introduce extra financial costs in the form of larger disk requirements and storage of backups.

The column genus will be the foreign key to the genus table. It is essential that the datatype of this column matches the one in the parent table (3). In the absence of any other information, we can use the same datatype definition for the species and common_name columns (4).

Notice in passing that spaces are not allowed in table or column names. common_name illustrates the use of the underscore character in place of a space to maintain readability.

All columns are defined as not null with the exception of common_name. This is because not every species will necessarily have a common name.

Defining a foreign key

To define the foreign key, you need to switch to the advanced view in the dialog. Once there, click on the Foreign Keys item as shown below (1). The fields are initially blank, but some are populated with default values when you click Add (2). You can accept these default values unless you have a particular reason for wanting to change the name of the constraint.

The next important step is to specify the referenced table (3) and the referenced constraint (4). Recall that a foreign key always matches the primary key in the related table. This rule is enforced in Oracle by paring a foreign key constraint with the primary key constraint in the parent. For single-column constraints this is not particularly helpful, but in the case of composite keys it allows the DBMS to make sure that all key columns are referenced in the relationship.

Having completed the definition of the constraint, the related columns are displayed in the lower panel (5). This lets you do a final check to make sure this is what you intended. Notice that in this case, the column name in one table is the same as the related column in the other table. This is not a requirement - you can name columns however you like - but maintaining a set of conventions makes the database structure easier to understand. Matching key names is one such convention; including the table name in the name of a synthetic key column is another. For example, species_id will be used in foreign key relationships with other tables. If we had just used id as the column name, it would either be difficult to interpret in that context, or we would have to rename it and then our related columns would not match.

Before completing the table creation by clicking OK, take a look at the generated DDL (6) and take a little time to understand each line.

CREATE TABLE SPECIES
(
SPECIES_ID NUMBER NOT NULL
, GENUS VARCHAR2(25) NOT NULL
, SPECIES VARCHAR2(25) NOT NULL
, COMMON_NAME VARCHAR2(25)
, CONSTRAINT SPECIES_PK PRIMARY KEY
(
SPECIES_ID
)
ENABLE
);

ALTER TABLE SPECIES
ADD CONSTRAINT SPECIES_GENUS_FK1 FOREIGN KEY
(
GENUS
)
REFERENCES GENUS
(
GENUS
)
ENABLE;

Notice that SQL Developer has generated two statements rather than one. You know from the lecture that you can add a foreign key constraint as part of the create table statement, so why not do that here?

One answer is that it is often useful to split these two operations up when preparing a schema maintenance script. When creating a child table, the parent table must exist before you can create the foreign key constraint - you have seen this in operation in this exercise. Therefore, in a script you would need to ensure that tables are created in a precise order. Alternatively, you could create all of your tables without constraints and add the foreign keys later using alter table statements. That way, you do not have to worry about ordering the tables.

In this schema we have examples of tables where one column has the same name as the table itself. When we also introduce the convention of using matching names for key fields, the generated code can become difficult to read. It is therefore important that you understand the syntax of the DDL statements. The syntax for the alter table statement above is

alter table <table name>
add constraint <constraint name> foreign key
( <local column name(s)> )
references <related table name> ( <related column name(s)> );

Thus the reference to genus on line 17 is the local column (the one in species), the reference on line 19 is the name of the related table, and the reference on line 21 is the name of the related column in genus.

If you are in any doubt about statement syntax, you can refer to the Oracle SQL language documentation where you will find many useful examples.