This section builds on the prior section describing secondary key indices to show how to open foreign key indices. A foreign key index is a secondary key index that also provides integrity constraints. When the primary key of a record in one database is embedded in the value of a record in another database, integrity constraints ensure that the record in the first database exists, i.e, that there are no "dangling pointers". In this example the Shipment's PartNumber and SupplierNumber fields will be used as foreign keys.

When a foreign key index is defined, an "delete action" parameter is specified. This parameter determines what action is taken by the Sleepycat Java Collections API when the record is deleted to which a foreign key refers. For example, consider what happens to a Shipment record when a Part or Supplier record that is referred to by that Shipment is deleted. There are three possibilities.

The SampleDatabase class is extended to open the Shipment-by-Part and Shipment-by-Supplier secondary key indices.

import com.sleepycat.bind.serial.SerialSerialKeyCreator;
import com.sleepycat.je.SecondaryConfig;
import com.sleepycat.je.SecondaryDatabase;
...
public class SampleDatabase
{
    ...
    private static final String SHIPMENT_PART_INDEX = "shipment_part_index";
    private static final String SHIPMENT_SUPPLIER_INDEX = 
        "shipment_supplier_index";
    ...
    private SecondaryDatabase shipmentByPartDb;
    private SecondaryDatabase shipmentBySupplierDb;
    ...
    public SampleDatabase(String homeDirectory)
        throws DatabaseException, FileNotFoundException
    {
        ...
        SecondaryConfig secConfig = new SecondaryConfig();
        secConfig.setTransactional(true);
        secConfig.setAllowCreate(true);
        secConfig.setSortedDuplicates(true);
        ...
        secConfig.setForeignKeyDatabase(partDb);
        secConfig.setForeignKeyDeleteAction(ForeignKeyDeleteAction.CASCADE);
        secConfig.setKeyCreator(
            new ShipmentByPartKeyCreator(javaCatalog,
                                         ShipmentKey.class,
                                         ShipmentData.class,
                                         PartKey.class));
        shipmentByPartDb = env.openSecondaryDatabase(null, 
                                                     SHIPMENT_PART_INDEX,
                                                     shipmentDb,
                                                     secConfig);

        secConfig.setForeignKeyDatabase(supplierDb);
        secConfig.setForeignKeyDeleteAction(ForeignKeyDeleteAction.CASCADE);
        secConfig.setKeyCreator(
            new ShipmentBySupplierKeyCreator(javaCatalog,
                                             ShipmentKey.class,
                                             ShipmentData.class,
                                             SupplierKey.class));
        shipmentBySupplierDb = env.openSecondaryDatabase(null,
                                                     SHIPMENT_SUPPLIER_INDEX,
                                                     shipmentDb,
                                                     secConfig);
    }
} 

If you compare these statements for opening foreign key indices to the statements used in the previous section for opening a secondary index, you'll notice that the only significant difference is that the setForeignKeyDatabase() and setForeignKeyDeleteAction() methods are called. setForeignKeyDatabase() specifies the foreign database that contains the records to which the foreign keys refer; this configures the secondary database as a foreign key index. setForeignKeyDeleteAction() specifies the delete action.

The application-defined ShipmentByPartKeyCreator and ShipmentBySupplierKeyCreator classes are shown below. They were used above to configure the secondary database objects.

public class SampleDatabase
{
    private static class ShipmentByPartKeyCreator
        extends SerialSerialKeyCreator
    {
        private ShipmentByPartKeyCreator(ClassCatalog catalog,
                                         Class primaryKeyClass,
                                         Class valueClass,
                                         Class indexKeyClass)
        {
            super(catalog, primaryKeyClass, valueClass, indexKeyClass);
        }

        public Object createSecondaryKey(Object primaryKeyInput,
                                         Object valueInput)
        {
            ShipmentKey shipmentKey = (ShipmentKey) primaryKeyInput;
            return new PartKey(shipmentKey.getPartNumber());
        }
    }

    private static class ShipmentBySupplierKeyCreator
        extends SerialSerialKeyCreator
    {
        private ShipmentBySupplierKeyCreator(ClassCatalog catalog,
                                             Class primaryKeyClass,
                                             Class valueClass,
                                             Class indexKeyClass)
        {
            super(catalog, primaryKeyClass, valueClass, indexKeyClass);
        }

        public Object createSecondaryKey(Object primaryKeyInput,
                                         Object valueInput)
        {
            ShipmentKey shipmentKey = (ShipmentKey) primaryKeyInput;
            return new SupplierKey(shipmentKey.getSupplierNumber());
        }
    }
} 

The key creator classes above are almost identical to the one defined in the previous section for use with a secondary index. The index key fields are different, of course, but the interesting difference is that the index keys are extracted from the key, not the value, of the Shipment record. This illustrates that an index key may be derived from the primary database record key, value, or both.

Note that the SerialSerialKeyCreator.nullifyForeignKey method is not overridden above. This is because ForeignKeyDeleteAction.NULLIFY was not used when creating the SecondaryDatabase objects. If it were used, implemnting the nullifyForeignKey() methods would be needed to set the part number and supplier number to null in the Shipment key. But record keys cannot be changed! And in fact, the primary key is not passed to the SerialSerialKeyCreator.nullifyForeignKey() method, only the primary value is passed. Therefore, if a foreign index key is derived from the primary key, ForeignKeyDeleteAction.NULLIFY may not be used.

The following getter methods return the secondary database objects for use by other classes in the example program.

public class SampleDatabase
{
    ...
    public final SecondaryDatabase getShipmentByPartDatabase()
    {
        return shipmentByPartDb;
    }

    public final SecondaryDatabase getShipmentBySupplierDatabase()
    {
        return shipmentBySupplierDb;
    }
} 

The following statements close the secondary databases.

public class SampleDatabase
{
    ...
    public void close()
        throws DatabaseException {

        supplierByCityDb.close();
        shipmentByPartDb.close();
        shipmentBySupplierDb.close();
        partDb.close();
        supplierDb.close();
        shipmentDb.close();
        javaCatalog.close();
        env.close();
    }
} 

Secondary databases must be closed before closing their associated primary database.