Automatic Key Generation

Automatic Key Generation
lac31
API Server automatically generates primary keys. You can also create your own primary keys that use database sequences. This page describes how API Server generates the primary key.
For more information about how to create primary keys that use database sequences, see the Oracle Sequence Example API Sample.
In this article:
 
 
2
 
 
Auto-Generate Primary Keys
Most modern database management systems (DBMSs) support automatic generation of primary keys, or surrogate keys. Within 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. A 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 API Server 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 Server 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 Server obtains the 
    Order#
     from the DBMS for the inserted order and "stamps" into each line item.
For more information about client refresh and REST APIs, including the update response, and about how to create and insert a set of rows in a single transmission, see POST.
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.
The following image shows the 
deal/notes
 model:
  CA Technologies  
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:
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"
}
}
]
}
]
CA Live API Creator
 designates that some of the data is inserted (not updated) using the 
INSERT
 metadata action tag. 
CA Live 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 complex transaction processing, including the 
INSERT
 metadata action tag, see INSERT Metadata Action Tag.
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
}
]
}
]