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 Creatorreturns 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 Creatorobtains theOrderI#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:
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:
- About how to define a resource explicitly in API Creator, see Customize your API.
- About the many-to-many relationship, see Examples of Relationships.
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 parentnote.
- Issues it before thedeal_has_notesrow.
- Copies the DBMS-generated key into thedeal_has_notesrow.
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 } ] }]