locked
Header & Posting table data insertion RRS feed

  • Question

  • User356503442 posted

    Hello All,

    Suppose there is a HeaderID in Header table and that is a foreign key in posting table. There can be multiple rows for same HeaderID in header table. So, combination of LineNo & HeaderID is used as primary key in posting table.

    I am confused about how to insert line numbers in LineNo column ? Should I make it an autoincrementing field like below:

    LineNo HeaderID

    1        1

    2        1

    3        2

    4        3

    Or it should increment individually for a perticular HeaderID?

    Ex:

    LineNo HeaderID

    1        1

    2        1

    1        2

    2        2

    3        2

    1        3

    If I use second approach, then is it ok to put the logic for line no in stored proceedure, rather than passing the value of Line No from code ?

    Please guide.

    Thanks.

    Tuesday, September 13, 2011 11:37 AM

Answers

  • User269602965 posted

    Your Primary Key, in this case the Line Number column should be UNIQUE in reach row.

    Autoincrementiing or use of SEQUENCE database object works for this purpose, routinely used by many developers.

    You can use other unique identifiers like GUID.

    Trying to manage referential integrity by programmatically generated keys is more prone to human error,

    than AUTO INCREMENTING or SEQUENCE database object values.

    The foreign key, in this case, the HeaderID does not need to be unique in this table. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2011 12:35 PM