0

I'm having a bit of an issue with circular foreign keys. I have a table posts with a uid column that is a BIGINT NOT NULL PRIMARY KEY, and I need to reference that in the last_seen_post column of my users table. The problem is that there's a sender_id column in posts that references users(user_id), meaning that I can't use both foreign keys at the same time because depending on the order of table definition, one of them will not be defined. Any fixes other than to drop the foreign key on the last_seen_post column?

I really would rather avoid dropping either foreign keys because the data integrity for this backend is mission critical. I know I could probably make another table to achieve this, but I was really hoping to get away with this since it's way cheaper in terms of space and search time

For context, the last_seen_post column is supposed to help the backend figure out what the user's "feed" is, i.e. the collection of posts from users they follow that they have not downloaded yet.

Nocturn9X
  • 25
  • 7

1 Answers1

0

Alright, I figured it out, so I'm gonna leave the solution here if others need it in the future. The easiest thing to do is to create the column without the foreign key constraint and then do ALTER TABLE yourtbl ADD FOREIGN KEY (yourcolumn) REFERENCES othertbl(othercolumn); once both tables have been created!

Nocturn9X
  • 25
  • 7