Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎Resources‎ > ‎REST APIs‎ > ‎

Primary Key Generation

Most modern DBMSs support automatic generation of primary (surrogate) keys.  This affects a number of very common scenarios, which are addressed by CA Live API Creator as described below.


Obtaining the Generated Key

When inserting a row whose unique key is generated by the DBMS, the application needs to obtain the key in order to later retrieve or update the data.  So,  API Creator returns this in the update response.

Master/Detail Add

When inserting a set of related rows (e.g., 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.  So, 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.  See the deal / notes model shown here (schema available at bottom of this page).

Now 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:


Insert a Note for a current Deal

Use the Resource DealWithNotes 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"
    }
    }
    ]
  }
]

Notes:
  1. Observe that the operation is a PUT

  2. We are using Complex Transaction Processing to designate that some of the data is inserted (not updated)

  3. Observe the SQL handling provided by  API Creator is rather complex:

    1. The system "looks ahead" to detect the insertion of the parent note, and

    2. Issues it before the deal_has_notes row, so that it can

    3. Copy the DBMS-generated key into the deal_has_notes row


Insert a Note for a some referenced Deal

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

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