---
title: "12: People and indexes | SurrealDB University"
description: "Employee assignments, REFERENCE, and DEFINE INDEX including UNIQUE constraints."
url: https://surrealdb.com/learn/schemas/page-12
---

![Course content preview](https://surrealdb.com/assets/static/course-schemas.D4CFbBhP.avif)

[Back to Courses](https://surrealdb.com/learn)

Course chapters

[Schema internals and migrations](https://surrealdb.com/learn/schemas) Internals [1: Schemaless vs. schemafull](https://surrealdb.com/learn/schemas/page-01) [2: Schema internals](https://surrealdb.com/learn/schemas/page-02) [3: Migrations](https://surrealdb.com/learn/schemas/page-03) [4: Data types](https://surrealdb.com/learn/schemas/page-04) [5: Automation](https://surrealdb.com/learn/schemas/page-05) Migrations [6: SurrealKit and the first table](https://surrealdb.com/learn/schemas/page-06) [7: Activities and seed data](https://surrealdb.com/learn/schemas/page-07) [8: Computed and asserted fields](https://surrealdb.com/learn/schemas/page-08) [9: Graph dependencies](https://surrealdb.com/learn/schemas/page-09) [10: Sync vs rollouts](https://surrealdb.com/learn/schemas/page-10) [11: Milestones](https://surrealdb.com/learn/schemas/page-11) [12: People and indexes](https://surrealdb.com/learn/schemas/page-12) [13: Events and CI](https://surrealdb.com/learn/schemas/page-13) [14: Capstone](https://surrealdb.com/learn/schemas/page-14)

# 12: People and indexes

The [project planning sample](https://surrealdb.com/docs/learn/schema-management/schema-design/sample-industry-schemas#project-planning) assigns work to employees, and it leans on `indexes` to keep lookups fast and data honest. This chapter adds people to the schema, then looks at `REFERENCE` and `DEFINE INDEX`, including `UNIQUE` constraints.

Note

SurrealKit-wise, nothing new lands here. You already know `sync` for a disposable local database and `rollout plan → start → complete` for a shared one (parts [10](https://surrealdb.com/learn/schemas/page-10)–[11](https://surrealdb.com/learn/schemas/page-11)). Put the definitions below into `database/schema/`, then apply them the same way you applied `milestone`. The new material is the SurrealQL: employees, `REFERENCE`, and indexes.

## Employee table

Add a table for the people doing the work. Create `database/schema/employee.surql`:

```surql
DEFINE TABLE employee SCHEMAFULL;
DEFINE FIELD name ON employee TYPE string;
DEFINE FIELD email ON employee TYPE option<string> ASSERT $value IS NONE OR string::is_email($value);
```

## Assigning activities to people

An activity will not always have someone assigned yet, so the field on `activity` needs to allow for that. Add this to `database/schema/activity.surql`:

```surql
DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>;
```

`option` means that "nobody assigned yet" is a valid state, not a missing field. If a later requirement says every activity must have an owner, you would tighten this the same way you tightened fields earlier in the course: backfill the data, then `ALTER` (part [3](https://surrealdb.com/learn/schemas/page-03)).

Create an employee and assign them to an existing activity so you have something to query:

```surql
CREATE employee:ada SET name = "Ada", email = "ada@example.com";
UPDATE activity:kickoff SET assigned_to = employee:ada;
```

## REFERENCE and what happens on delete

As things stand, nothing stops you deleting `employee:ada` even though an activity points at her. If you would rather SurrealDB refused that delete, add a `REFERENCE` clause. Replace the `assigned_to` line you just wrote rather than adding a second one, for the reason given in part [8](https://surrealdb.com/learn/schemas/page-08): one `DEFINE FIELD` per table and field name, or the metadata key collides.

```surql
DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>
    REFERENCE ON DELETE REJECT;
```

With `employee:ada` still assigned to `activity:kickoff`, the delete is now refused:

```surql
DELETE employee:ada;
```

Output

```surql
'Cannot delete `employee:ada` as it is referenced by `activity:kickoff` with an ON DELETE REJECT clause'
```

| Clause | Effect |
| --- | --- |
| `REFERENCE` `ON DELETE REJECT` | Cannot delete `employee` while an `activity` points at them |
| `ON DELETE CASCADE` | Deleting the parent removes or clears children (use carefully) |
| `ON DELETE IGNORE` | Parent delete allowed; links may dangle unless you clean up |

`REFERENCE` is catalog metadata that SurrealDB enforces on delete. That is different from a bare `record<>` field (part [4](https://surrealdb.com/learn/schemas/page-04)), which just stores a pointer and does nothing special when the record it points to disappears.

## Indexes for the queries you actually run

### Lookup by project name

```surql
DEFINE INDEX project_name ON project FIELDS name;
```

This supports `WHERE name = …` on a long list of projects. It does not need to be `UNIQUE` unless project names must be distinct.

### Unique milestone per project

```surql
DEFINE INDEX milestone_name_per_project ON milestone FIELDS project, name UNIQUE;
```

`UNIQUE` indexes stop duplicates at write time, which is exactly what you want for `(project, name)`. Before adding one on a table that already has data, check for duplicates first (part [3](https://surrealdb.com/learn/schemas/page-03)).

We can do this by filtering the grouped result with an outer `SELECT`:

```surql
SELECT * FROM (
    SELECT project, name, count() AS total FROM milestone GROUP BY project, name
) WHERE total > 1;
```

On a small table you can also just read the counts and skip the outer filter:

```surql
SELECT project, name, count() AS total FROM milestone GROUP BY project, name;
```

If either returns records, backfill or rename the duplicates in a data script before you add the index, whether that is a local `sync` or a `rollout start` on a shared database.

### Activity date ranges

Scheduling queries usually filter on `start` or `end`:

```surql
DEFINE INDEX activity_start ON activity FIELDS start;
```

Composite indexes are worth adding once your actual query patterns justify them. See [DEFINE INDEX](https://surrealdb.com/docs/reference/query-language/statements/define/indexes) for the full syntax.

## ASSERT or a UNIQUE index?

| Mechanism | Enforces | Also helps |
| --- | --- | --- |
| `ASSERT` on a field | Rules for a single record | n/a |
| `UNIQUE` `index` | No duplicate combinations across records | Fast lookups |

Uniqueness of `(project, name)` is a cross-row rule, so it belongs on an `index`, not a handwritten `ASSERT` over a subquery.

## Indexes and SurrealKit

Adding an `index` is non-destructive, so it is safe on `rollout start` (or a local `sync`). Dropping one is destructive, so save that for `rollout complete`, once nothing depends on it any more.

```bash
surrealkit sync --user root --pass secret --ns main --db main   # local
# or
surrealkit rollout plan --name add_planning_indexes
```

## Checkpoint

- `REFERENCE` `ON DELETE REJECT` (or whichever delete policy fits)
- `DEFINE INDEX` for lookups, `UNIQUE` where duplicates would be a bug

Previous

11: Milestones

[Previous](https://surrealdb.com/learn/schemas/page-11)

Next lesson

13: Events and CI

[Next lesson](https://surrealdb.com/learn/schemas/page-13)

```json
{"@context":"https://schema.org","@type":"Course","name":"Schema internals and migrations","description":"Learn how SurrealDB stores schema metadata, how DEFINE statements shape your database, and how to migrate production data safely.","url":"https://surrealdb.com/learn/schemas","inLanguage":"en","isAccessibleForFree":true,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"Schema internals and migrations","url":"https://surrealdb.com/learn/schemas"},{"@type":"LearningResource","name":"1: Schemaless vs. schemafull","url":"https://surrealdb.com/learn/schemas/page-01"},{"@type":"LearningResource","name":"2: Schema internals","url":"https://surrealdb.com/learn/schemas/page-02"},{"@type":"LearningResource","name":"3: Migrations","url":"https://surrealdb.com/learn/schemas/page-03"},{"@type":"LearningResource","name":"4: Data types","url":"https://surrealdb.com/learn/schemas/page-04"},{"@type":"LearningResource","name":"5: Automation","url":"https://surrealdb.com/learn/schemas/page-05"},{"@type":"LearningResource","name":"6: SurrealKit and the first table","url":"https://surrealdb.com/learn/schemas/page-06"},{"@type":"LearningResource","name":"7: Activities and seed data","url":"https://surrealdb.com/learn/schemas/page-07"},{"@type":"LearningResource","name":"8: Computed and asserted fields","url":"https://surrealdb.com/learn/schemas/page-08"},{"@type":"LearningResource","name":"9: Graph dependencies","url":"https://surrealdb.com/learn/schemas/page-09"},{"@type":"LearningResource","name":"10: Sync vs rollouts","url":"https://surrealdb.com/learn/schemas/page-10"},{"@type":"LearningResource","name":"11: Milestones","url":"https://surrealdb.com/learn/schemas/page-11"},{"@type":"LearningResource","name":"12: People and indexes","url":"https://surrealdb.com/learn/schemas/page-12"},{"@type":"LearningResource","name":"13: Events and CI","url":"https://surrealdb.com/learn/schemas/page-13"},{"@type":"LearningResource","name":"14: Capstone","url":"https://surrealdb.com/learn/schemas/page-14"}]}
```

```json
{"@context":"https://schema.org","@type":"LearningResource","name":"12: People and indexes","description":"Employee assignments, REFERENCE, and DEFINE INDEX including UNIQUE constraints.","url":"https://surrealdb.com/learn/schemas/page-12","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"Schema internals and migrations","url":"https://surrealdb.com/learn/schemas"},"position":15}
```

```json
{"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","legalName":"SurrealDB Ltd","identifier":{"@type":"PropertyValue","propertyID":"GB-COH","value":"13615201"},"address":{"@type":"PostalAddress","streetAddress":"3rd Floor, 1 Ashley Road","addressLocality":"Altrincham","addressRegion":"Cheshire","postalCode":"WA14 2DT","addressCountry":"GB"},"contactPoint":[{"@type":"ContactPoint","contactType":"customer support","email":"support@surrealdb.com","url":"https://surrealdb.com/contact","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"sales","email":"info@surrealdb.com","url":"https://surrealdb.com/contact","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"security","email":"security@surrealdb.com","url":"https://surrealdb.com/.well-known/security.txt","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"legal","email":"legal@surrealdb.com","url":"https://surrealdb.com/legal","availableLanguage":"English"}],"hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]}
```

```json
{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Page 12","item":"https://surrealdb.com/learn/schemas/page-12"}]}
```