Which table should be filled first with a minimum cardinality of 1..1 using referential integrity?

Question

Say I'm using referential integrity and I have a relationship between two entity's (A and B), and the minimum cardinality on both sides is 1. That would mean before table A can be filled, table B needs a record that table A can be linked to. But since the minimum cardinality is 1 on both sides, the same can be said the other way around, i.e. there needs to be a record in table A that a record in table B can be linked with, before the record can be inserted in table B.

Now that seems problematic, since if I understand correctly, referential integrity forces you to link the record to another record in the other table, in both scenario's, meaning I cant enter any records in either tables...

Can someone explain what would happen in this scenario?

I asked this same question to my teacher, and she says a relationship with a minimum of cardinality 1..1 (is this notation correct?) is certainly possible, but she was unable to explain to me which table should be filled first.

Sorry I don't have any concrete examples, I was just thinking about this randomly... If you can elaborate by using a practical example in your answer, that would be great.

Answer 1

Your case is not seems correct.

In one to one relationship there must be one table parent and other one is child.

Lets consider You have one user table and a user profile table. In this case user_profile belongs to user. You can not create user_profile until you have user. i.e. User table is parent and user_profile is child. And belong to will contain id of parent table.

In user and user_profile scenario user_profile table will hold user_id and you need to create user object first then pass this id to user_profile and create user_profile.

So in your scenario you need to find which you make parent and which you make child. If this not the case you will always in chicken egg stuff.

Answer 2

With this question you are making the assumption that from a user's perspective a DBMS can manipulate only a single table at once, when this is not the case.

At a Conceptual level it is certainly possible to have two entities with a cardinality of one to one.

At a Logical level in a Relational Data Model this would mean the set of values of a candidate key in one relation must precisely equal the set of values of a candidate key in another relation.

At a Physical level in most DBMSs this integrity constraint would be implemented as foreign key constraints.

In most DBMSs it is indeed the case that only one table can be manipulated at once.

In this case, if the constraints are checked after every statement then one table must be chosen as the 'parent' and one as the 'child'. Manipulation of the tables must be done in the correct order so the foreign key constraint is not violated after any statement. Additional validation may be required in triggers, etc to ensure for each parent there is exactly one child.

However, if the checking of the constraints can be deferred so they are not checked until changes are committed, two foreign key constraints could be specified; one between the first table and the second and the reverse between the second table and the first. The manipulation of the tables could be done in any order so long as, by the time the changes were committed, neither constraint was violated. However, in the middle of a multi-statement transaction from the user's perspective the constraint is violated.

There is also the concept of multiple assignment. This is were the manipulation of more than one table is seen as a single atomic operation. In this case the single statement would need to manipulate both tables such that the constraint was not violated at the end of the statement. In this case, from the user's perspective the constraint is, and can never be, violated. There are non-commercial DBMSs that support these multiple assignments.

You may wish to read Chris Date's and Hugh Darwen's Third Manifesto which introduced the concept of multiple assignment.