How to handle cyclic foreign key insertion?

Question

I have a table 'release_group' and another table 'release', like so:

release_group 
------------------------
release_group_id (PRIMARY KEY)
name
main_release_id (FOREIGN KEY)

release
------------------------
release_id (PRIMARY KEY)
release_group_id (FOREIGN KEY)
name

If i create a release row, the release_group_id is not available, simply because it hasn't been created yet. But i can't create a release_group row without the main_release_id. So it's kind of a no way situation.

EDIT: The main_release_id in release_group is to say that the release is the main from the group, the one i will use a reference.

What is the professional way of handling this case?

1. Remove the foreign key index main_release_id in table release_group and give an attribute ALLOW NULL. Create the release_group row so i can applied it's id in the release row.

2. Keep the 'main_release_id' foreign key index in table 'release_group' and assign it a temporary integer value of 0. Create the release row and update the foreign key in the release_group accordingly? Keep in mind that if this is the way to go, i could end up inadvertently having foreign keys with value 0, which i don't know if this ethic in a database?

What is the professional way of handling this case?

Thanks

Answer 1

Seeing how a release cannot belong to more than one group, you could remove the complication by:

1. Dropping the main_release_id field altogether

2. Add a release_main field to the release table; it would be NULL (not main) or 1 (main).

3. Add a UNIQUE constraint on (release_group_id, release_main) to make sure there can only be one main release.

Update

If a release can belong to multiple groups, you would need to create a many-to-many table and move the foreign key in both tables into it:

(release_group_id [FK], release_id [FK], is_main)

The primary key would span the first two fields. Ensuring that there can only be one main release requires a spanning unique key over the first and last field.

Old answer

Assuming main_release_id is a nullable field, what I would suggest is the following:

1. Insert the release_group with main_release_id being null; get last inserted id.

2. Insert the release entry, passing the id of the previous step; get last inserted id.

3. Update the release_group table entry, setting the value of main_release_id to the value you got from the previous step.

4. Commit transaction

Alternatively, you could use sequences so that you know the id before inserting the entries. See an example of this in the manual under LAST_INSERT_ID().