# CHECK constraints

> Write rules that every row of a table must follow, including rules that follow links into other tables.

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

`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`**

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

Answer:

```json
{
  "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`**

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

**`insert_row`**

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

Refused:

```text
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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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_"
}
```

## 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`**

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

Answer:

```json
{
  "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`**

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

Refused:

```text
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`**

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

Refused:

```text
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`**

```json
{
  "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:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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]]"
    }
  ]
}
```