Is there a way to auto increment a column with respect to the foreign key in DB2?

Question

Say I have an address table with the addresses of different facilities of a manufacturing company. The foreign key lets me know which company the addresses belong to, but i need a surrogate id to differentiate between each facility. This id should increment automatically based on the foreign key value. Note : I just need simple integer values for keys.

eg:

My table has the following columns, ORGANIZATION_ID is the foreign key. FACILITY_ID is a second surrogate key dependednt on the foreign key.

     ADDRESS_TABLE
    ->ORGANIZATION_ID
    ->FACILITY_ID
    ->ADDRESS_LINE_1
    ->ADDRESS_LINE_2
    ->CITY
    ->STATE
    ->ZIP_CODE

I want the facility id to increase automatically from 1 depending on the organization id. i.e

    ORGANIZATION_ID 1
    FACILITY_ID 1

When I insert data for new organization, facility should start from 1

    ORGANIZATION_ID 2
    FACILITY_ID 1

Next time I insert data for the same organization, my facility id should increment accordingly -

    ORGANIZATION_ID 1
    FACILITY_ID 2

Is there any way to make this happen in DB2? I'm currently on DB2 V 10.5.6

Answer 1

No. Auto-increment, or Identity keys as DB2 calls them, don't support composite keys.

Best you could do would be to have a on insert trigger that handled assigning the values you want. Possibly making use of a SEQUENCE; though you'd have to create a new sequence to use for the facilities of each new organization.