locked
Make composite columns as a foreign key in its own table RRS feed

  • Question

  • User2108892867 posted

    Hello everyone, normally when I use datatype int as my primary key, I can design my table to something like this:

    application_id (primary key), run_id, date_create, application_name, parent_application_id (nullable)

    The parent_application_id is a foreign key of the application_id in the same table. 

    Now I have a situation where I need to take out application_id and use composite columns (run_id, date_create) to create a primary key to something like this:

    run_id, date_create, application_name, ???

    How do I design the parent_application_id in this case? Is it possible?

    Thanks. 

    Monday, January 21, 2019 2:35 AM

Answers

  • User753101303 posted

    Hi,

    Then  you would need parent_run_id, parent_date_create and parent_application_name so that this fk triplet can match your pk triplet...

    Make sure this is needed. Maybe a unique index could be enough or this table should be splitted ? (not sure what you call a "parent" application, this is to handle installing multiple instances of a single application ?)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 21, 2019 6:06 PM

All replies

  • User753101303 posted

    Hi,

    Then  you would need parent_run_id, parent_date_create and parent_application_name so that this fk triplet can match your pk triplet...

    Make sure this is needed. Maybe a unique index could be enough or this table should be splitted ? (not sure what you call a "parent" application, this is to handle installing multiple instances of a single application ?)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 21, 2019 6:06 PM
  • User-1174608757 posted

    Hi asplearning ,

    According to your description ,if you want to use  composite columns (run_id, date_create) to create a primary key , you should also create  columns like  parent run_id,parent date_create  as composite foreign key. 

    Your table should at least:

    run_ id , date_create ,application_name, parent run_id, parent date_create

    Best Regards

    Wei Zhang

    Tuesday, January 22, 2019 3:27 AM