Opening Foreign Key Indices
Prev | Chapter 3. Using Secondary Indices and Foreign keys | Next

## Opening Foreign Key Indices

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 Edition 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.

- ForeignKeyDeleteAction.ABORT specifies that the transaction should be aborted by throwing an exception. The effect is that deleting a Part or Supplier that is referred to by one or more Shipments will not be possible. The Sleepycat Java Edition will automatically throw a DatabaseException, which should normally cause the transaction to be aborted during exception processing. This is the default delete action if none is specified.

- ForeignKeyDeleteAction.NULLIFY specifies that the Part or Supplier Number field in the Shipment record should be cleared, or set to a null or empty value. The effect is that the deleted Part or Supplier will no longer be referenced by any Shipment record. This option applies when the foreign key field is optional, i.e, when the application allows it to be set to a null or empty value. When using this option, the application must implement the `nullifyForeignKey()` method of the ForeignKeyNullifier interface.

- ForeignKeyDeleteAction.CASCADE specifies that the Shipment record should be deleted also. The effect is that deleting a Part or Supplier will delete all Shipments for that Part or Supplier. This option applies when the deleted record is considered the "parent" or "owner" of the record containing the foreign key, and is used in this example. Since deleting the Shipment record could cause other deletions if other records contain the foreign key of the Shipment, and so on, the term "cascade" is used to describe the effect.

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.ForeignKeyDeleteAction;
    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(StoredClassCatalog 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(StoredClassCatalog 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, implementing 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.

Prev | Up | Next
Chapter 3. Using Secondary Indices and Foreign keys | Home | Creating Indexed Collections