Create a schema step by step
Build a table with fields, a searchable category and a supplier relationship.
Create two linked tables: each product names a supplier that must already exist.
- id
- p001
- name
- Desk lamp
- supplier_id
- s001
- id
- s001
- name
- North Supply
Accepted: supplier_id points to an existing supplier.
The relation names the link; the foreign key checks that its destination exists.
- Define the supplier tableGive each supplier a unique ID.
- Define the product tableAdd its supplier reference and required validation rules.
- Insert a supplier firstMake the referenced ID available.
- Add and verify the productUse that supplier ID, then check the saved row and relationship.
Before you start
Use a running database and copy its complete OC_URL and OC_TOKEN from Connect. See connection setup.
Save the supplier definition as suppliers.toml
namespace = "shop"
table = "suppliers"
primary_key = ["id"]
[[columns]]
name = "id"
ty = "str"
required = true
[[columns]]
name = "name"
ty = "str"Build the definition
| Step | Add | Purpose |
|---|---|---|
| 1 | namespace, table, primary_key | Name the table and identify each record. |
| 2 | [[columns]] | Store the product's name, category, price and supplier ID. |
| 3 | [[indexes]] | Make a category index available to the query planner. |
| 4 | [[relations]] | Give the supplier connection a name: supplied_by. |
| 5 | [[foreign_keys]] | Reject a product that points to a missing supplier. |
| 6 | [[check_constraints]] | Require a positive price and a known category. |
This example stores the price as integer cents. For exact decimal text, review the decimal type in the schema reference.
Save the finished schema
Save the following as manifest.toml. It includes all six steps, so you do not need to combine separate snippets.
Complete product schema
# manifest.toml - the finished schema.
namespace = "shop"
table = "products"
primary_key = ["id"]
[[columns]]
name = "id"
ty = "str"
required = true
[[columns]]
name = "name"
ty = "str"
[[columns]]
name = "category"
ty = "str"
[[columns]]
name = "price_cents"
ty = "i64"
[[columns]]
name = "supplier_id"
ty = "str"
[[indexes]]
name = "by_category"
columns = ["category"]
[[relations]]
name = "supplied_by"
from_col = "supplier_id"
bidirectional = true
[relations.target]
namespace = "shop"
table = "suppliers"
pk = "id"
[[foreign_keys]]
name = "fk_supplier"
from_col = "supplier_id"
target_schema = "shop.suppliers"
target_col = "id"
on_delete = "restrict"
[[check_constraints]]
name = "price_positive"
expression = "price_cents > 0"
[[check_constraints]]
name = "category_in_set"
expression = "category IN ('electronics', 'books', 'shoes')"Register suppliers.toml first, then manifest.toml; a definition creates the table, not its records.
Run the example: register, write and read back
Run these commands from the folder containing both files. Set OC_URL and OC_TOKEN first; use a new database or unused table names for this example.
1. Register both tables. An existing table returns 409; inspect it before continuing.
curl --fail-with-body "$OC_URL/schemas" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: text/plain; charset=utf-8" \
--data-binary @suppliers.toml
curl --fail-with-body "$OC_URL/schemas" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: text/plain; charset=utf-8" \
--data-binary @manifest.toml2. Save the supplier, then its product. Continue only after each request succeeds.
curl --fail-with-body "$OC_URL/rows/shop.suppliers" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
--data '{"id":"s001","name":"North Supply"}'
curl --fail-with-body "$OC_URL/rows/shop.products" \
-H "Authorization: Bearer $OC_TOKEN" \
-H "Content-Type: application/json" \
--data '{"id":"p001","name":"Desk lamp","category":"electronics","price_cents":1990,"supplier_id":"s001"}'3. Read the product. Look for id: p001, supplier_id: s001 and price_cents: 1990 in the returned record.
curl --fail-with-body "$OC_URL/rows/shop.products/p001" \
-H "Authorization: Bearer $OC_TOKEN"These are sample commands, not results from your database. See record operations for replacement and update behaviour.
Check the rules
| Try | Expected behaviour |
|---|---|
| A product with a valid supplier, positive price and allowed category | The row can be saved. |
| A product with an unknown supplier ID | The foreign-key check rejects it. |
| A negative or zero price | The price check rejects it. |
| Deleting a supplier still used by a product | on_delete = "restrict" prevents the deletion. |
Try Missing supplier in the illustration above to see the foreign-key rule. The price and category rules are separate checks.
Add search when you need it
The schema does not automatically create embeddings or index text. Use the vector and full-text setup guides for those steps.