Automatic Key Generation

Automatic Key Generation
calac41
 
Layer7 Live API Creator
 automatically generates primary 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
. You can also create your own primary keys that use database sequences. This article describes how 
Layer7 Live API Creator
 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. Automatic-generation of primary keys affects some common scenarios. The logic for a typical application 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 
Layer7 Live API Creator
 places the key into each line item.
You can use 
Layer7 Live API Creator
 for the following common patterns:
  • When inserting a row that has a unique DBMS-generated key, the client application must obtain the key so that it can later retrieve or update the data. 
    Layer7 Live 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. 
    Layer7 Live API Creator
     obtains the 
    OrderI#
     from the DBMS for the inserted order and "stamps" into each line item.
For more information:
  • About client refresh and REST APIs and about how to create and insert a set of rows in a single transmission, see POST.
  • About how to use sequences in data sources that support them, such as Oracle and Microsoft SQL Server, see the documentation for that data source, for example, Oracle Data Source.
Add Junction Records
Relational DBMSs require the introduction of a junction table, or link, for many-to-many parent-child 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 explicitly define resources in API Creator over junction tables.
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"
}
}
]
}
]
Layer7 Live API Creator
 designates that some of the data is inserted (not updated) using the 
INSERT
 metadata action tag. 
Layer7 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 note to an existing deal. POST the following code snippet into 
NoteForDeal
 :
[
{
"note_name": "New Note",
"deal_has_notesList": [
{
"deal_id": 1000
}
]
}
]