0

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

Ash
  • 33
  • 2
  • 9
  • @Esperento57 I've updated my question again. I've been posting this in between my tight schedule..sorry for the errors.. – Ash Dec 20 '16 at 10:15
  • 1
    why want you do it? Just create a column autoincrement on you table ADDRESS_TABLE as primary key, you should have primary key on all your tables. Its enought for unicity and integrity – Esperento57 Dec 20 '16 at 10:30

1 Answers1

2

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.

Charles
  • 21,637
  • 1
  • 20
  • 44