# Checks

<Callout type="info" title="Where this fits">
An [index](/docs/define/table/indexes) does not reject a row. A check does. A [picklist](/docs/define/table/shape) already adds its own check.
</Callout>

`checks` is an object. Each key is a name you choose. Each value receives column handles and returns `sql` text. `schema()` stores that text. This is not a query builder. A return that is not `sql` text is OKM1060: `Check notes.title_present must return sql text.`

<Callout type="info" title="SQL name">
`sql` inserts a handle as the SQL name `schema()` stores, unquoted. A [casing](/docs/define/schema/casing) of `"snake"` has already been applied. A string is inserted as you wrote it. A number or a boolean is inserted as text.
</Callout>

### Name

The key is not the constraint name. The constraint is `{table}_{key}_check`. Snake casing does not rewrite the key.

#### key

The key below is `title_present`, so the constraint is `notes_title_present_check`.

```ts title="name.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
  },
  {
    checks: {
      title_present: (columns) => sql`${columns.title} <> ''`, // [!code focus]
    },
  },
);
```

#### rename

Keep that key when you rename a column the check uses. The constraint stays `tasks_present_check`. The plan renames the column and does not drop the constraint.

```ts title="rename.ts"
import { sql, table, t } from "okmodel/pg";

export const tasks = table(
  "tasks",
  {
    id: t.identity(),
    contact: t.text().renamedFrom("email"), // [!code focus]
  },
  {
    checks: {
      present: (columns) => sql`${columns.contact} <> ''`, // [!code focus]
    },
  },
);
```

A new table adds the check with `alter table`. A table that already exists adds it `not valid`, then validates it.

A row that fails the check is `Check violation on notes (title_present)`. The kind is `check`. `constraint` is `notes_title_present_check`.

<Callout type="error" title="OKM1023">
A picklist check is `{table}_{sqlColumn}_check`. A key equal to that SQL column name is `Duplicate identity for constraint notes_title_check.`
</Callout>

<Callout type="error" title="OKM1122">
An empty key is `Name key is empty.`
</Callout>

### Expression

The function receives column handles and returns `sql` text.

#### one

One column compares the stored value with SQL you write.

```ts title="one-column.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
  },
  {
    checks: {
      title_present: (columns) => sql`${columns.title} <> ''`, // [!code focus]
    },
  },
);
```

#### two

Two handles compare two columns. This check is `notes_ends_after_start_check`, and the text is `endsAt > startsAt` when those are the SQL names.

```ts title="two-columns.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    startsAt: t.timestamptz(),
    endsAt: t.timestamptz(),
  },
  {
    checks: {
      ends_after_start: (columns) => sql`${columns.endsAt} > ${columns.startsAt}`, // [!code focus]
    },
  },
);
```

#### number

A number is inserted as text. This expression is `rank >= 1`.

```ts title="number.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    rank: t.integer(),
  },
  {
    checks: {
      rank_positive: (columns) => sql`${columns.rank} >= ${1}`, // [!code focus]
    },
  },
);
```

#### text

Quotes around text belong in the template. `` sql`${columns.status} <> 'draft'` `` is `status <> 'draft'`. Interpolating the string `"draft"` inserts `draft` with no quotes.

```ts title="text.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    status: t.text(),
  },
  {
    checks: {
      not_draft: (columns) => sql`${columns.status} <> 'draft'`, // [!code focus]
    },
  },
);
```

#### several

Several checks are several keys. Each key is its own constraint: `notes_title_present_check` and `notes_rank_positive_check`.

```ts title="several.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    title: t.text(),
    rank: t.integer(),
  },
  {
    checks: {
      title_present: (columns) => sql`${columns.title} <> ''`, // [!code focus]
      rank_positive: (columns) => sql`${columns.rank} >= ${1}`, // [!code focus]
    },
  },
);
```

#### sql name

A [SQL name](/docs/define/table/names#sql-name) you set is the name inside the expression. Here the column is `published_at` and the table is `note`, so the constraint is `note_after_open_check` and the text uses `published_at`.

```ts title="sql-name.ts"
import { sql, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    openedAt: t.timestamptz(),
    publishedAt: t.timestamptz().sqlName("published_at"), // [!code focus]
  },
  {
    sqlName: "note", // [!code focus]
    checks: {
      after_open: (columns) => sql`${columns.publishedAt} > ${columns.openedAt}`,
    },
  },
);
```

#### casing

`"snake"` rewrites a name you did not set. `publishedAt` is `published_at` in the expression. The key stays `after_epoch`, so the constraint is `notes_after_epoch_check`.

```ts title="casing.ts"
import { schema, sql, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  {
    id: t.identity(),
    publishedAt: t.timestamptz(), // [!code focus]
  },
  {
    checks: {
      after_epoch: (columns) => sql`${columns.publishedAt} > '2020-01-01'`,
    },
  },
);

export const app = schema({
  tables: [notes],
  casing: "snake", // [!code focus]
});
```