Adding two entities with FKs pointing to each other

Question

I have two tables in my application. The first one, Sections, stores content for a specific section of an online publication. This contains (among other things) the section title and a field CurrentRevisionId (FK).

A second table, Revisions, stores different revisions of that content. It has PK RevisionId field (the FK above, used to denote current revision), the HTML content itself, and SectionId (FK), specifying which Section the revision belongs to.

So basically I've got these two FKs pointing to each other. When I create a new Section, the child entity CurrentRevision is also populated with that content. But the InsertOnSubmit fails with this error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sections_Revisions". The conflict occurred in database "xxxx", table "xxxx.Sections", column 'SectionID'. The statement has been terminated.

My guess is that it can't simultaneously create both rows and populate them with each other's PKs.

Should I insert the Section first, then the Revision (along with Section's PK), and then update the Section with the Revision PK? Or is there a better / more elegant solution to this?

EDIT: I've attached a screencap of the two tables with their relationships. Each Section has many Revisions (FK SectionID inside of Revisions table). Additionally, there is a one-to-one relationship where the CurrentRevisionId field in Section points to the revision which is "current". This "current" revision is where the application pulls the HTML from. Hope that clears things up a bit.

Answer 1

You can't have FK's from 2 tables pointing at each other; it makes no sense.

If you are trying to model a many-to-many relationship, then you will need to create a mapping table inbetween the two tables. It will contain (at a minimum) two Foreign Keys, one to each of your 2 tables primary keys.