How to limit the referenced data to existing ids (FOREIGN KEY style)?

[szabgab]

I built the following example based on one of the example in the sdk folder. One table referencing another table.

It works nice, but I can create an entry that reference an ID that does NOT exists in the other table. This part:

 classes.push(DanceClass {
        id: Thing::from((DANCE, Id::rand())),
        name: String::from("Belly dance"),
    });

The create works, but the net SELECT with the fetch fails.

Is there a way to ensure I cannot even create the entry? That is, in the code below I'd expect the add_students to fail.

Full code

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::sql::{Id, Thing};
use surrealdb::Surreal;

const DANCE: &str = "dance";
const STUDENT: &str = "student";

#[derive(Debug, Serialize, Deserialize)]
struct DanceClass {
    id: Thing,
    name: String,
}

#[derive(Debug, Serialize, Deserialize)]
struct Student {
    id: Thing,
    name: String,
    classes: Vec<Thing>,
}

#[derive(Debug, Deserialize)]
#[allow(dead_code)]
struct StudentClasses {
    id: Thing,
    name: String,
    classes: Vec<DanceClass>,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let db = Surreal::new::<Mem>(()).await?;

    db.use_ns("namespace").use_db("database").await?;

    add_classes(&db).await?;
    let mut classes = get_classes(&db).await?;
    classes.push(DanceClass {
        id: Thing::from((DANCE, Id::rand())),
        name: String::from("Belly dance"),
    });


    add_students(&db, classes).await?;

    show_students_in_classes(&db).await?;

    Ok(())
}

async fn add_classes(db: &Surreal<Db>) -> surrealdb::Result<()> {
    for name in ["Introduction to Dancing", "Flamenco"] {
        let classes: Vec<DanceClass> = db
        .create(DANCE)
        .content(DanceClass {
            id: Thing::from((DANCE, Id::rand())),
            name: name.to_owned(),
        })
        .await?;
        println!("class added: {classes:?}");

    }

    Ok(())
}

async fn get_classes(db: &Surreal<Db>) -> surrealdb::Result<Vec<DanceClass>> {
    let sql = format!("SELECT * FROM dance");
    let mut results = db.query(sql).await?;
    let classes: Vec<DanceClass> = results.take(0)?;

    for class in &classes {
        println!("get_classes: {class:?}");
    }

    Ok(classes)
}

async fn add_students(db: &Surreal<Db>, classes: Vec<DanceClass>) -> surrealdb::Result<()> {
    let students: Vec<Student> = db
        .create(STUDENT)
        .content(Student {
            id: Thing::from((STUDENT, Id::rand())),
            name: "Jane Doe".to_owned(),
            classes: classes.into_iter().map(|class| class.id).collect(),
        })
        .await?;
    println!("student added: {students:#?}");

    Ok(())
}

async fn show_students_in_classes(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let sql = format!("SELECT * FROM {STUDENT} FETCH classes");
    let mut results = db.query(sql).await?;
    let students: Vec<StudentClasses> = results.take(0)?;
    println!("Students: {students:#?}");

    Ok(())
}

The output

class added: [DanceClass { id: Thing { tb: "dance", id: String("3djg22rmkdf6exo84yum") }, name: "Introduction to Dancing" }]
class added: [DanceClass { id: Thing { tb: "dance", id: String("fzdd8ibgp9pt671x1ulo") }, name: "Flamenco" }]
get_classes: DanceClass { id: Thing { tb: "dance", id: String("3djg22rmkdf6exo84yum") }, name: "Introduction to Dancing" }
get_classes: DanceClass { id: Thing { tb: "dance", id: String("fzdd8ibgp9pt671x1ulo") }, name: "Flamenco" }
student added: [
    Student {
        id: Thing {
            tb: "student",
            id: String(
                "6p2pil6lgzm0apfpz7c2",
            ),
        },
        name: "Jane Doe",
        classes: [
            Thing {
                tb: "dance",
                id: String(
                    "3djg22rmkdf6exo84yum",
                ),
            },
            Thing {
                tb: "dance",
                id: String(
                    "fzdd8ibgp9pt671x1ulo",
                ),
            },
            Thing {
                tb: "dance",
                id: String(
                    "ywfrchgwyi0k6iswk2zj",
                ),
            },
        ],
    },
]
Error: Api(FromValue { value: Array(Array([Object(Object({"classes": Array(Array([Object(Object({"id": Thing(Thing { tb: "dance", id: String("3djg22rmkdf6exo84yum") }), "name": Strand(Strand("Introduction to Dancing"))})), Object(Object({"id": Thing(Thing { tb: "dance", id: String("fzdd8ibgp9pt671x1ulo") }), "name": Strand(Strand("Flamenco"))})), None])), "id": Thing(Thing { tb: "student", id: String("6p2pil6lgzm0apfpz7c2") }), "name": Strand(Strand("Jane Doe"))}))])), error: "invalid type: null, expected struct DanceClass" })

[dbaudisch]

You can adjust your schema to only allow existing Record IDs.

For v2.0.0 and later:

DEFINE FIELD classes ON student TYPE array<record<dance>>;
DEFINE FIELD OVERWRITE classes.* ON student TYPE record<dance> ASSERT $value.exists();

Up until v1.5.5:

DEFINE FIELD classes ON student TYPE array<record<dance>>;
DEFINE FIELD classes.* ON student TYPE record<dance> ASSERT !!$value.id;