--- 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) Course chapters [Back to courses](https://surrealdb.com/learn) [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 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>; ``` `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): with two definitions for one table and field name, the second silently replaces the first. ```surql DEFINE FIELD assigned_to ON activity TYPE option> 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. Remember that SurrealKit applies whole files. Whenever a later change touches `activity.surql`, the file is applied again with `DEFINE INDEX … OVERWRITE`, and SurrealDB rebuilds `activity_start` from scratch even though the index itself did not change. That costs nothing on a course database, but on a large table it is a reason to keep indexes in a file of their own. ```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 At a glance **You are on** Chapter 12 of 14 **Chapters** 14 **Format** Text, with queries you can run **Runs in** SurrealDB Studio, in the browser **Cost** Free **Then take** [A tour of SurrealDB](https://surrealdb.com/learn/tour) [Next: 13: Events and CI](https://surrealdb.com/learn/schemas/page-13) ## Continue ### [11: Milestones](https://surrealdb.com/learn/schemas/page-11) Previous ### [13: Events and CI](https://surrealdb.com/learn/schemas/page-13) Next lesson THE PLATFORM ## Everything an application and its agents know. Five surfaces, one engine. Database Document, graph, vector, time-series and relational in one engine. ![Five data models as dotted tiles: documents, graph, vector, time-series and relational](https://surrealdb.com/assets/static/platform-database.DUdumYDz.avif) Read more [Database](https://surrealdb.com/surrealdb) Agent Memory What an agent learns, with its source and its time, in the same engine. ![A timeline of remembered facts, each with its source](https://surrealdb.com/assets/static/platform-agent-memory.Cvv8D5Wl.avif) Read more [Agent Memory](https://surrealdb.com/agent-memory) Cloud Managed clusters in the regions you choose, scaled on demand. ![Clusters in three regions on a world map, each running or scaling](https://surrealdb.com/assets/static/platform-cloud.--QZnaVi.avif) Read more [Cloud](https://surrealdb.com/cloud) Studio Query, explore and design the schema from the browser. ![A SurrealQL query in Studio and the schema graph under it](https://surrealdb.com/assets/static/platform-studio.7ykBFNLq.avif) Read more [Studio](https://surrealdb.com/studio) MCP Every model that speaks MCP reaches the database and the memory directly. ![Three models connected through MCP to the database and Agent Memory](https://surrealdb.com/assets/static/platform-mcp.D_oH0_wm.avif) Read more [MCP](https://surrealdb.com/mcp) IN PRODUCTION ## Trusted at scale. Samsung, Nvidia, Verizon, Tencent, and Walmart run on SurrealDB. 14,000+ Developers building on SurrealDB Cloud 4M+ Developers building on SurrealDB worldwide FROM THE TEAMS > SurrealDB gives us a foundation where we can unify semantic search, knowledge graphs, and AI-driven decision making without stitching together multiple systems. Collapsing responsibility into SurrealDB has become our default engineering posture. *Justin Foley* VP of Engineering, Later ```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","@id":"https://surrealdb.com/#organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the context and memory layer for AI agents. 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"}]} ```