Invoking APIs‎ > ‎POST‎ > ‎

Automatic Key Generation

API Creator can automatically generate the primary key or you can use database sequences that compute the primary key. This page describes how API Creator automatically generates primary keys.

For more information about how to use database sequences to compute the primary key by viewing an API project sample, see Database Sequences API Project Sample.

Auto-generate Primary Keys

Most modern database management systems (DBMSs) support automatic generation of primary keys, or surrogate keysWithin a relationship, the table containing the primary key on the "one" side is the parent. For example, Purchaseorder is a parent to Lineitem.

Automatic-generation of primary keys affects a number of common scenarios, which Live API Creator addresses. Typical application's logic often includes logic to handle DBMS-generated keys. For example, you want to add an order and line items in a single transaction. The Order# is generated into the database and Live API Creator places the key into each line item.

You can use API Creator for the following common patterns:

  • When inserting a row that has a unique DBMS-generated key, the client application needs to obtain the key so that it can later retrieve or update the data. API Creator returns the key in the update response.
  • When inserting a set of related rows (for example, an order header and a set of line items), insert the set in a single transmission (Cascade Add). This reduces latency and provides transaction bracketing. API Creator obtains the Order# from the DBMS for the inserted order and "stamps" into each line item. 

Add Junction Records

Relational DBMSs require the introduction of a junction (Link) table for many-to-many relationships, containing a foreign key of each of the related tables. A junction table is a table with foreign keys to both endpoints. For example, in the Sample Database, orders can have many Products and a Product can be ordered on many Orders. This sample database includes the Junction Lineitem which adds foreign keys to both, as well as additional attributes (such as QuantityOrdered).

The following image shows the deal/notes model:

Consider inserting one (or several) Deal Notes with new notes, where you require a single message that inserts both the notes and deal_has_notes to reduce latency and the inserts are a single transaction.

You can add junction records by creating custom resources.

For more information about creating custom resources, see Customize your API.

Insert a New Note for a Current Deal

You can display deals and their notes using the DealWithNotes resource. To add a new note for a current Deal, PUT the following JSON:

[
  {
    "deal_id": 1000,
    "deal_name": "Deal 1",
    "@metadata": {
      "href": "https://.../v1/DealWithNotes/1000",
      "checksum": "A:aef56611747973ef"
    },
    "deal_has_notesList": [
    {
      "@metadata": {"action": "INSERT"},
    "note": 
    {
              "@metadata": {"action": "INSERT"},
    "note_name": "New Note"
    }
    }
    ]
  }
]

API Creator designates that some of the data is inserted (not updated) using the INSERT metadata action tag. API Creator provides the following SQL handling:

  • "Looks ahead" to detect the insertion of the parent note.
  • Issues it before the deal_has_notes row.
  • Copies the DBMS-generated key into the deal_has_notes row.
For more information about the complex transaction processing, including the INSERT metadata action tag, see Insert Metadata Action.

Insert a Note for a Referenced Deal

Alternatively, you can add a new note to an existing deal. POST the following into NoteForDeal:

[
  {
    "note_name": "New Note",
    "deal_has_notesList": [
     {
     "deal_id": 1000
     }
    ]
  }
]

More Information

For more information:

  • About client refresh and REST APIs, including the update response, and about creating and inserting a set of rows in a single transmission, see POST.
  • About Cascade Add and using them with primary keys, see the Database Sequences API Project Sample.