Skip to content
Datanotes

CHECK constraints

A CHECK constraint is a rule written as a where filter, such as “a watched film has a rating”. Every row of the table must pass it: the engine refuses a write that breaks it, and refuses to add a constraint that rows already break. A rule can follow links into other tables.

Enforce rules with CHECK constraints

Section titled “Enforce rules with CHECK constraints”

add_constraint adds a CHECK constraint, a rule every row must satisfy, written as a where filter. The engine refuses it while existing rows break the rule and lists them; after that, it enforces the rule on every write. A condition on a column the row leaves empty passes, as in Structured Query Language (SQL). Inside or, and and not, the engine tests an empty value as it is, so a watched film without a rating breaks rated_when_watched.

add_constraint

{
  "table": "film",
  "name": "rated_when_watched",
  "check": { "or": [{ "status": { "ne": "watched" } }, { "rating": { "exists": true } }] }
}

Answer:

{
  "ok": true,
  "op": "add_constraint",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 8, "operation": "ADD CONSTRAINT" }
  ],
  "migration_id": "20261012T165235-add_constraint-add-constraint-rated_when_watc"
}

add_constraint

{ "table": "film", "name": "at_most_three_hours", "check": { "minutes": { "lte": 180 } } }

insert_row

{
  "table": "film",
  "values": {
    "title": "His Girl Friday",
    "year": 1940,
    "minutes": 92,
    "status": "watched"
  }
}

Refused:

insert_row refused — nothing was written:
invalid datanotes/diary/classics/film/His Girl Friday.md: rated_when_watched violates CHECK rated_when_watched: {"or":[{"status":{"ne":"watched"}},{"rating":{"exists":true}}]}
Hint: The row breaks a CHECK constraint of the table (named in the message, with its rule): change the values so the rule holds. describe_table lists the table's constraints. Then call insert_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/data-quality/check-constraints.md#enforce-rules-with-check-constraints (offline: read_docs("guides/data-quality/check-constraints#enforce-rules-with-check-constraints"))

insert_row

{
  "table": "film",
  "values": {
    "title": "His Girl Friday",
    "year": 1940,
    "minutes": 92,
    "status": "watched",
    "rating": 5
  }
}

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/diary/classics/film/His Girl Friday.md"],
  "commits": [{ "table": "diary.classics.film", "version": 10, "operation": "WRITE" }],
  "migration_id": "20261012T165235-insert_row-insert-into-film-2"
}

drop_constraint removes a constraint, here the three-hour limit, which a long restoration could break:

drop_constraint

{ "table": "film", "name": "at_most_three_hours" }

Answer:

{
  "ok": true,
  "op": "drop_constraint",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 11, "operation": "DROP CONSTRAINT" }
  ],
  "migration_id": "20261012T165235-drop_constraint-drop-constraint-at_most_three_"
}
Section titled “Write a CHECK rule that follows links”

A CHECK constraint is a rule every row must satisfy, and you add one with add_constraint. It can follow a link the way a query does: parent.kind is the kind of the row that parent names. Here a genre’s parent must be an era, an era’s parent must be Cinema, and Cinema has no parent.

A rule treats links as follows:

  • Empty link: passes at the top level of a rule and fails inside or, and and not; on a longer path such as parent.parent.kind, the rule skips only an empty first link
  • Link to a row that doesn’t exist: fails, so it passes inside not
  • List of links: every linked row must match
  • Resolution: links resolve as in the vault, by path from the row’s note

add_constraint

{
  "table": "genre",
  "name": "parent_by_level",
  "check": {
    "or": [
      { "kind": "cinema", "parent": { "exists": false } },
      { "kind": "era", "parent.kind": "cinema" },
      { "kind": "genre", "parent.kind": "era" }
    ]
  }
}

Answer:

{
  "ok": true,
  "op": "add_constraint",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.genre", "version": 4, "operation": "ADD CONSTRAINT" }
  ],
  "migration_id": "20261012T165221-add_constraint-add-constraint-parent_by_level"
}

The engine refuses a genre straight under Cinema:

insert_row

{
  "table": "genre",
  "values": { "name": "Horror", "kind": "genre", "parent": "[[Cinema]]" }
}

Refused:

insert_row refused — nothing was written:
invalid datanotes/main/public/genre/Horror.md: parent_by_level violates CHECK parent_by_level: {"or":[{"kind":"cinema","parent":{"exists":false}},{"kind":"era","parent.kind":"cinema"},{"kind":"genre","parent.kind":"era"}]}
Hint: The row breaks a CHECK constraint of the table (named in the message, with its rule): change the values so the rule holds. describe_table lists the table's constraints. Then call insert_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/data-quality/check-constraints.md#enforce-rules-with-check-constraints (offline: read_docs("guides/data-quality/check-constraints#enforce-rules-with-check-constraints"))

When a write changes a linked row, the engine checks the rows that link to it again. Sound film can’t become a top level like Cinema while a genre hangs from it, and the refusal lists Screwball comedy, naming Sound film.

update_row

{
  "table": "genre",
  "path": "datanotes/main/public/genre/Sound film.md",
  "set": { "kind": "cinema" },
  "unset": ["parent"]
}

Refused:

update_row refused — nothing was written:
invalid datanotes/main/public/genre/Screwball comedy.md: parent_by_level violates CHECK parent_by_level: {"or":[{"kind":"cinema","parent":{"exists":false}},{"kind":"era","parent.kind":"cinema"},{"kind":"genre","parent.kind":"era"}]} (after this change to [[Sound film]], a row it links to)
Hint: The row breaks a CHECK constraint of the table (named in the message, with its rule): change the values so the rule holds. describe_table lists the table's constraints. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/data-quality/check-constraints.md#enforce-rules-with-check-constraints (offline: read_docs("guides/data-quality/check-constraints#enforce-rules-with-check-constraints"))

In a batch, add the era first and then its genre. The engine checks each step against the vault as the earlier steps left it, so Slapstick comedy finds its parent.

batch

{
  "summary": "add the silent era and slapstick comedy",
  "operations": [
    {
      "op": "insert_row",
      "table": "genre",
      "values": { "name": "Silent cinema", "kind": "era", "parent": "[[Cinema]]" }
    },
    {
      "op": "insert_row",
      "table": "genre",
      "values": {
        "name": "Slapstick comedy",
        "kind": "genre",
        "parent": "[[Silent cinema]]"
      }
    }
  ]
}

Answer:

{
  "ok": true,
  "op": "batch",
  "dry_run": false,
  "migration_id": "20261012T165221-batch-add-the-silent-era-and-slapsti",
  "steps": [
    {
      "index": 0,
      "op": "insert_row",
      "ok": true,
      "migration_id": "20261012T165221-insert_row-insert-into-genre-4",
      "result": {
        "ok": true,
        "op": "insert_row",
        "files_created": 1,
        "planned_creates": ["datanotes/main/public/genre/Silent cinema.md"],
        "commits": [{ "table": "main.public.genre", "version": 5, "operation": "WRITE" }],
        "migration_id": "20261012T165221-insert_row-insert-into-genre-4"
      }
    },
    {
      "index": 1,
      "op": "insert_row",
      "ok": true,
      "migration_id": "20261012T165221-insert_row-insert-into-genre-5",
      "result": {
        "ok": true,
        "op": "insert_row",
        "files_created": 1,
        "planned_creates": ["datanotes/main/public/genre/Slapstick comedy.md"],
        "commits": [{ "table": "main.public.genre", "version": 6, "operation": "WRITE" }],
        "migration_id": "20261012T165221-insert_row-insert-into-genre-5"
      }
    }
  ]
}

select_rows

{ "table": "genre", "columns": ["name", "kind", "parent"], "order_by": "name" }

Answer:

{
  "table": "main.public.genre",
  "total": 5,
  "rows": [
    {
      "path": "datanotes/main/public/genre/Cinema.md",
      "name": "Cinema",
      "kind": "cinema",
      "parent": null
    },
    {
      "path": "datanotes/main/public/genre/Screwball comedy.md",
      "name": "Screwball comedy",
      "kind": "genre",
      "parent": "[[Sound film]]"
    },
    {
      "path": "datanotes/main/public/genre/Silent cinema.md",
      "name": "Silent cinema",
      "kind": "era",
      "parent": "[[Cinema]]"
    },
    {
      "path": "datanotes/main/public/genre/Slapstick comedy.md",
      "name": "Slapstick comedy",
      "kind": "genre",
      "parent": "[[Silent cinema]]"
    },
    {
      "path": "datanotes/main/public/genre/Sound film.md",
      "name": "Sound film",
      "kind": "era",
      "parent": "[[Cinema]]"
    }
  ]
}

Renaming a row that other rows link to keeps the rule valid, because the links follow the row. Here Slapstick comedy follows the era to its new name, Silent film.

update_row

{
  "table": "genre",
  "path": "datanotes/main/public/genre/Silent cinema.md",
  "set": { "name": "Silent film" }
}

Answer:

{
  "ok": true,
  "op": "update_row",
  "notes_changed": 1,
  "files_moved": 1,
  "planned_edits": [
    {
      "path": "datanotes/main/public/genre/Silent cinema.md",
      "set": { "name": "Silent film" },
      "remove": []
    }
  ],
  "planned_moves": [
    {
      "from": "datanotes/main/public/genre/Silent cinema.md",
      "to": "datanotes/main/public/genre/Silent film.md"
    }
  ],
  "commits": [{ "table": "main.public.genre", "version": 7, "operation": "UPDATE" }],
  "migration_id": "20261012T165221-update_row-update-genre"
}

select_rows

{
  "table": "genre",
  "columns": ["name", "parent"],
  "where": { "parent.kind": "era" },
  "order_by": "name"
}

Answer:

{
  "table": "main.public.genre",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/genre/Screwball comedy.md",
      "name": "Screwball comedy",
      "parent": "[[Sound film]]"
    },
    {
      "path": "datanotes/main/public/genre/Slapstick comedy.md",
      "name": "Slapstick comedy",
      "parent": "[[Silent film]]"
    }
  ]
}