How to handle cyclic foreign key insertion?

Question

I have a table 'release_group' and another table 'release' , for example:

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 line, release_group_id is not available, simply because it has not been created yet. But I cannot create a release_group string without main_release_id . So this is not a random situation.

EDIT: The main_release_id in the release_group should say that the release is the main of the group, the one that I will use the link.

What is the professional way to handle this case?

1. Remove the foreign key index main_release_id in the release_group table and specify the ALLOW NULL attribute. Create a release_group string so that I can apply its id to the release string.

2. Save the foreign key index 'main_release_id' in the table 'release_group' and assign it a temporary integer value of 0. Create a release line and update the foreign key in release_group accordingly? Keep in mind that if this is the way, I can accidentally get foreign keys with a value of 0, which I don’t know if this ethics is in the database?

What is the professional way to handle this case?

thanks

Answer 1

Seeing that a release cannot belong to more than one group, you can remove the complexity with:

• Drop the main_release_id field in general

• Add the release_main field to the release table; it will be NULL (not primary) or 1 (primary).

• Add a UNIQUE to (release_group_id, release_main) to make sure there can only be one major version.

Update

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

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

The primary key will cover the first two fields. Ensuring that there can be only one major release requires a unique key in the first and last field.

Old answer

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

• Insert release_group with main_release_id to NULL ; get the last inserted id.

• Insert the release record, passing the identifier of the previous step; get the last inserted id.

• Update the record for the release_group table by setting the value of main_release_id to the value you obtained in the previous step.

• Commit transaction

Alternatively, you can use sequences so you know the identifier before inserting records. See an example of this in the guide under LAST_INSERT_ID() .

Answer 2

It looks like you are trying to create many, many relationships. To do this correctly, remove the foreign keys from the release_group and release tables. Add a new table (release_to_release_group) that contains the foreign key release_id and the foreign key of the release.

Edit: There is no need to use circular foreign keys here. Remove main_release_id foreign_key from release_group and add the is_main_release flag to the release table.

Answer 3

It is not a common practice for tables to refer to each other. Usually, the parent table (release_group) should have a reference to the foreign key in the child table (release), but it will not have access to release_id as a foreign key in the release_group table.

Perhaps add a Boolean flag to the release table to indicate that this is the main release, and delete the main_release_id file in the release_group.