How to avoid Inserting invalid values into a table with relations with mysql?

Using planetscale so I cant reference other columns My schema:

export const mainTable = mysqlTable("main",{
    id: serial("id").primaryKey().notNull(),
    name: varchar("name", {length:32}).unique().notNull()
})

export const subTable = mysqlTable("sub",{
    id: serial("id").primaryKey().notNull(),
    name: varchar("name", {length: 32}).unique().notNull(),
    mainName: varchar("main", {length:32}).notNull()
})

export const mainRelationsTable = relations(mainTable,({many})=>({
    sub:many(subTable),
}))

export const subRelationsTable = relations(subTable,({one})=>({
    main:one(mainTable,{
        fields:[subTable.mainName],
        references:[mainTable.name]
    })
}))

export const mainTable = mysqlTable("main",{
    id: serial("id").primaryKey().notNull(),
    name: varchar("name", {length:32}).unique().notNull()
})

export const subTable = mysqlTable("sub",{
    id: serial("id").primaryKey().notNull(),
    name: varchar("name", {length: 32}).unique().notNull(),
    mainName: varchar("main", {length:32}).notNull()
})

export const mainRelationsTable = relations(mainTable,({many})=>({
    sub:many(subTable),
}))

export const subRelationsTable = relations(subTable,({one})=>({
    main:one(mainTable,{
        fields:[subTable.mainName],
        references:[mainTable.name]
    })
}))

this query runs without problems:

await dbClient.insert(subTable).values({name: "something", mainName:"value that doesnt exist in main table"})

await dbClient.insert(subTable).values({name: "something", mainName:"value that doesnt exist in main table"})

how do I avoid this? can I make the query automatically return error without manually checking if mainName exists in mainTable first?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

How to avoid Inserting invalid values into a table with relations with mysql?