2 tables cannot reference each others columns?

I have the following tables declaration:

export const questionsTable = sqliteTable('questions', {
  // bla bla
  acceptedAnswerId: text('accepted_answer_id').references(() => answersTable.id, {onDelete: 'cascade'}), // ERROR HERE
})

export const answersTable = sqliteTable('answers', {
  // bla bla
  questionId: text('question_id')
    .notNull()
    .references(() => questionsTable.id, { onDelete: 'cascade' }), // THIS REFERENCE DOES NOT LET ME CREATE A REFERENCE TO answersTable.id IN questionsTable.acceptedAnswerId, LOOK ABOVE
})

export const questionsTable = sqliteTable('questions', {
  // bla bla
  acceptedAnswerId: text('accepted_answer_id').references(() => answersTable.id, {onDelete: 'cascade'}), // ERROR HERE
})

export const answersTable = sqliteTable('answers', {
  // bla bla
  questionId: text('question_id')
    .notNull()
    .references(() => questionsTable.id, { onDelete: 'cascade' }), // THIS REFERENCE DOES NOT LET ME CREATE A REFERENCE TO answersTable.id IN questionsTable.acceptedAnswerId, LOOK ABOVE
})

As you can see, for some reason I CANNOT reference one table's column from the other table, while this table's column is already referenced by the other table. Can you explain why so and what should I do?

7 Replies

GetPsyched•8mo ago

Use relations instead: https://orm.drizzle.team/docs/rqb#declaring-relations

piscopancer•8mo ago

@GetPsyched I use relations... I need to store the id of the correct answer anyway

GetPsyched•8mo ago

not asking you to not store the id just link the id using relations

Mykhailo•8mo ago

Hello, @piscopancer! Could you please clarify what you want to accomplish? What relations do you have between tables? I see that you have acceptedAnswerId, one question can have several answers, but answer can have only one question, is it right?

piscopancer•8mo ago

yes, I am building a QA service where a user ask a question, receives a bunch of answers from other users and picks one which they think is correct or satisfies them. So, one question has many answers and the accepted_answer_id (1,2,3,4... | null) while answers have a question_id column on which they exist. I was having a lively discussion on this issue on a typescript server and we figured out that the error I was getting was related to typescript itself being unable to infer the types of tables that mutually referenced each other, resulting in both of them being of type any (which sucks). For that a solution was found. See SQLiteColumn explicit type declaration

export const questionsTable = sqliteTable('questions', {
  // bla bla
  acceptedAnswerId: integer('accepted_answer_id').references((): SQLiteColumn => answersTable.id, {onDelete: 'set null'}),
})

export const questionsTable = sqliteTable('questions', {
  // bla bla
  acceptedAnswerId: integer('accepted_answer_id').references((): SQLiteColumn => answersTable.id, {onDelete: 'set null'}),
})

This fixes the type inference, it's us helping typescript, possibly... Anyway, odds are this issue is totally on the behalf of how typescript was used by the drizzle developers to help it infer types, which is not perfect, as you can see

Mykhailo•8mo ago

Ok, cool, this solution fits. Btw, you can use AnySQLiteColumn. Also, did you think about changing your table structure a bit? You have one-to-many relation. So, one question has got many answers and answer has only one question, so you can do smth like this:

export const questionsTable = sqliteTable('questions', {
  // bla bla
  // remove acceptedAnswerId
})

export const answersTable = sqliteTable('answers', {
  // bla bla
  questionId: text('question_id')
    .notNull()
    .references(() => questionsTable.id, { onDelete: 'cascade' }),
  accepted: boolean('accepted').default(false) // when users pick answer which they think is correct you will update accepted column value to true
})

export const questionsTable = sqliteTable('questions', {
  // bla bla
  // remove acceptedAnswerId
})

export const answersTable = sqliteTable('answers', {
  // bla bla
  questionId: text('question_id')
    .notNull()
    .references(() => questionsTable.id, { onDelete: 'cascade' }),
  accepted: boolean('accepted').default(false) // when users pick answer which they think is correct you will update accepted column value to true
})

or this solution doesn't fit well for your case?

piscopancer•8mo ago

And I will also have to apply false for the rest of the answers, if the user had previously marked one answer as true and then decided that there is a more fitting answer. Isn't it weird? there cannot be a chicken and egg problem. First comes the question, second comes the answer because every answer has a required foreign key question_id meanwhile a question can be on its own question's accepted_answer_id is nullable!!! which means it is NULL upon creation

Gaming

Programming

2 tables cannot reference each others columns?