---
title: "Schemafull CRUD | SurrealDB University"
description: "Schemafull CRUD. A chapter of SurrealDB Fundamentals, a hands-on course with runnable examples."
url: https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud
---

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

Course chapters

[SurrealDB Fundamentals](https://surrealdb.com/learn/fundamentals) [Introduction](https://surrealdb.com/learn/fundamentals) [Welcome to SurrealDB University](https://surrealdb.com/learn/fundamentals/intro/welcome) [Intro to SurrealDB](https://surrealdb.com/learn/fundamentals/intro/surrealdb) [Why SurrealQL is SQL-like](https://surrealdb.com/learn/fundamentals/intro/surrealql) [Part 1: Schemaless CRUD](https://surrealdb.com/learn/fundamentals/schemaless) [Introduction](https://surrealdb.com/learn/fundamentals/schemaless) [Record IDs](https://surrealdb.com/learn/fundamentals/schemaless/record-ids) [Inserting data](https://surrealdb.com/learn/fundamentals/schemaless/inserting-data) [Reading data](https://surrealdb.com/learn/fundamentals/schemaless/reading-data) [Updating data](https://surrealdb.com/learn/fundamentals/schemaless/updating-data) [Deleting data](https://surrealdb.com/learn/fundamentals/schemaless/deleting-data) [Part 2: Adding relationships](https://surrealdb.com/learn/fundamentals/relationships) [Introduction](https://surrealdb.com/learn/fundamentals/relationships) [Graph relations](https://surrealdb.com/learn/fundamentals/relationships/graph-relations) [Record links](https://surrealdb.com/learn/fundamentals/relationships/record-links) [Relational style joins](https://surrealdb.com/learn/fundamentals/relationships/relational-style) [Part 3: Making it schemafull](https://surrealdb.com/learn/fundamentals/schemafull) [Introduction](https://surrealdb.com/learn/fundamentals/schemafull) [Define tables, views and changefeeds](https://surrealdb.com/learn/fundamentals/schemafull/define-table) [Define fields, constraints and assertions](https://surrealdb.com/learn/fundamentals/schemafull/define-fields) [Schemafull CRUD](https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud) [Part 4: Making it secure](https://surrealdb.com/learn/fundamentals/security) [Introduction](https://surrealdb.com/learn/fundamentals/security) [Authentication](https://surrealdb.com/learn/fundamentals/security/authentication) [Query capabilities](https://surrealdb.com/learn/fundamentals/security/query-capabilities) [Part 5: Making it performant](https://surrealdb.com/learn/fundamentals/performance) [Introduction](https://surrealdb.com/learn/fundamentals/performance) [Indexing & data model considerations](https://surrealdb.com/learn/fundamentals/performance/index-data-model) [Deployment & storage layer considerations](https://surrealdb.com/learn/fundamentals/performance/deployment-storage) [Completion](https://surrealdb.com/learn/fundamentals/completion) Certification Pending completion

# Schemafull CRUD

Now that we've learned how to define both tables and fields, It's time for some schemafull CRUD.

In this lesson, we'll only cover what is different from schemaless CRUD, namely how to update the schema and data for tables and fields using:

- The `FIELD` clause on the `REMOVE` statement
- The `OVERWRITE` clause on the `DEFINE` statement
- The `ALTER` statement

## Adding and removing fields

When updating our schemaless tables, we could just `SET` or `UNSET` fields and they would appear. Not so for a `SCHEMAFULL` table, however.

```
UPDATE product:01FSXKCPVR8G1TVYFT4JFJS5WBSET new_field = "why not?";UPDATE product UNSET category;
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

In both of these cases we get a schema error. In the first case it's because the `new_field` field hasn't been defined yet, and in the second because `category` is a field that can't hold the value `NONE`, which is what `UNSET` is trying to do.

For our second `UPDATE` query which tries to `UNSET` the `category` field on our `product` table, we also get a schema error.

```
REMOVE FIELD category ON TABLE product;SELECT category FROM product limit 1;UPDATE product;SELECT * FROM product limit 1;
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

In order to remove a field from our schemafull table, we first need to use the `REMOVE FIELD` statement.

However, that is not enough to actually remove the field data, as the `REMOVE FIELD` statement only removes the schema definition.

Once the schema definition is removed, we can use `UNSET` like we did earlier.

There is however another way, if removing multiple fields, we can simply run `UPDATE product` and the `UPDATE` statement will remove anything that is no longer a part of the schema definition.

## Updating fields and tables

For updating existing schema definitions there are two options:

- The `OVERWRITE` clause on the `DEFINE` statement
- The `ALTER` statement.

For example, we can change the datatype of a field or change a table from schemafull to schemaless.

```
-- Change from a generic number type to floatDEFINE FIELD OVERWRITE price ON TABLE product TYPE float;-- Change from schemafull to schemalessDEFINE TABLE OVERWRITE product TYPE NORMAL SCHEMALESS;-- Change again from schemaless to schemafullALTER TABLE product SCHEMAFULL;
```

![Surrealist Icon](https://surrealdb.com/assets/static/fdfe2c20f5941d5e.D0guSOhZ.webp) Run Query

The difference between these two options is that

- `DEFINE ... OVERWRITE` can be used to both create new definitions and update existing ones, but needs to include the entire definition.
- The `ALTER` statement only updates existing definitions and only needs to include the items to be updated, not the entire definition.

## Summary

Let's summarise what we've learned.

When adding and removing fields:

- You need to use `DEFINE FIELD` to add the field to the schema before using `UPDATE` to add the data.
- You need to use `REMOVE FIELD` to remove the field from the schema before using `UPDATE` to remove the data.

When updating fields and tables:

- `DEFINE ... OVERWRITE` can be used to both create new definitions and update existing ones, but needs to include the entire definition.
- The `ALTER` statement only updates existing definitions and only needs to include the items to be updated, not the entire definition.

That's it for this part on making our data schemafull, in the next part, we'll explore how to make our data secure.

Previous

Define fields, constraints and assertions

[Previous](https://surrealdb.com/learn/fundamentals/schemafull/define-fields)

Next lesson

Part 4: Making it secure

[Next lesson](https://surrealdb.com/learn/fundamentals/security)

```json
{"@context":"https://schema.org","@type":"Course","name":"SurrealDB Fundamentals","description":"The most efficient way to learn SurrealDB through guided hands-on learning","url":"https://surrealdb.com/learn/fundamentals","inLanguage":"en","isAccessibleForFree":false,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals"},{"@type":"LearningResource","name":"Welcome to SurrealDB University","url":"https://surrealdb.com/learn/fundamentals/intro/welcome"},{"@type":"LearningResource","name":"Intro to SurrealDB","url":"https://surrealdb.com/learn/fundamentals/intro/surrealdb"},{"@type":"LearningResource","name":"Why SurrealQL is SQL-like","url":"https://surrealdb.com/learn/fundamentals/intro/surrealql"},{"@type":"LearningResource","name":"Part 1: Schemaless CRUD","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemaless"},{"@type":"LearningResource","name":"Record IDs","url":"https://surrealdb.com/learn/fundamentals/schemaless/record-ids"},{"@type":"LearningResource","name":"Inserting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/inserting-data"},{"@type":"LearningResource","name":"Reading data","url":"https://surrealdb.com/learn/fundamentals/schemaless/reading-data"},{"@type":"LearningResource","name":"Updating data","url":"https://surrealdb.com/learn/fundamentals/schemaless/updating-data"},{"@type":"LearningResource","name":"Deleting data","url":"https://surrealdb.com/learn/fundamentals/schemaless/deleting-data"},{"@type":"LearningResource","name":"Part 2: Adding relationships","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/relationships"},{"@type":"LearningResource","name":"Graph relations","url":"https://surrealdb.com/learn/fundamentals/relationships/graph-relations"},{"@type":"LearningResource","name":"Record links","url":"https://surrealdb.com/learn/fundamentals/relationships/record-links"},{"@type":"LearningResource","name":"Relational style joins","url":"https://surrealdb.com/learn/fundamentals/relationships/relational-style"},{"@type":"LearningResource","name":"Part 3: Making it schemafull","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/schemafull"},{"@type":"LearningResource","name":"Define tables, views and changefeeds","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-table"},{"@type":"LearningResource","name":"Define fields, constraints and assertions","url":"https://surrealdb.com/learn/fundamentals/schemafull/define-fields"},{"@type":"LearningResource","name":"Schemafull CRUD","url":"https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud"},{"@type":"LearningResource","name":"Part 4: Making it secure","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/security"},{"@type":"LearningResource","name":"Authentication","url":"https://surrealdb.com/learn/fundamentals/security/authentication"},{"@type":"LearningResource","name":"Query capabilities","url":"https://surrealdb.com/learn/fundamentals/security/query-capabilities"},{"@type":"LearningResource","name":"Part 5: Making it performant","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Introduction","url":"https://surrealdb.com/learn/fundamentals/performance"},{"@type":"LearningResource","name":"Indexing \u0026 data model considerations","url":"https://surrealdb.com/learn/fundamentals/performance/index-data-model"},{"@type":"LearningResource","name":"Deployment \u0026 storage layer considerations","url":"https://surrealdb.com/learn/fundamentals/performance/deployment-storage"},{"@type":"LearningResource","name":"Completion","url":"https://surrealdb.com/learn/fundamentals/completion"}]}
```

```json
{"@context":"https://schema.org","@type":"LearningResource","name":"Schemafull CRUD","description":"Schemafull CRUD","url":"https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"SurrealDB Fundamentals","url":"https://surrealdb.com/learn/fundamentals"},"position":22}
```

```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","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":"Schemafull crud","item":"https://surrealdb.com/learn/fundamentals/schemafull/schemafull-crud"}]}
```