Primary Key Generation


Most modern DBMSs support automatic generation of primary (surrogate) keys.  This affects a number of very common scenarios, which Live API Creator addresses.

Obtain the Generated Key

When inserting a row whose unique key is generated by the DBMS, 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.

For more information about client refresh and REST APIs, including the update response, see Post.

Master/Detail Add

When inserting a set of related rows (for example, an Order Header and a set of Line Items), it is desirable this be done in a single transmission. Not only does this reduce latency, but it provides transaction bracketing. This is sometimes called a Cascade Add.

API Creator provides automated support for this common pattern. For example, it obtains the Order# from the DBMS for the inserted order, and "stamps" into each Line Item.  Click here for more information.

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. The following image shows the deal / notes model.

Consider inserting one (or several) Deal Notes with new notes, where we require:
  1. A single message inserts both the notes and deal_has_notes to reduce latency, and further
  2. All of the inserts are a single transaction
You can define custom resources to address these requirements. The following image shows how to define a custom resource:

Insert a Note for a Current Deal

Use the DealWithNotes resource to display deals and their notes. 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"
    }
    }
    ]
  }
]

This operation is a PUTComplex Transaction Processing is used to designate that some of the data is inserted (not updated). API Creator provides the following SQL handling:
  1. "Looks ahead" to detect the insertion of the parent note.
  2. Issues it before the deal_has_notes row.
  3. Copies the DBMS-generated key into the deal_has_notes row.

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
    }
    ]
  }
]




User Generated Keys

You can also generate keys with code in conjunction with database services, as described here.