Solved In a "One to one relationship can the child record be left empty with the key in the Parent table ?

sethugec29

New member
Local time
Tomorrow, 00:17
Joined
Feb 9, 2021
Messages
10
In a "One to one" relationship can the child record be left empty with the key...so the the record can be fill in at a later stage. What I have noticed it that at least one field of the child record must be filled out in order for the a actual record to be created in child table...
 
its a no, you need to add some info on at least a field.
although you can Manually insert a record (using SQL Insert, inserting the PK key)
 
Let's address the more basic problem. There is almost (I DID say "almost") no case where you would want a true 1/1 table relationship, and it is for the reason you stated. If you have relational integrity enabled, you MUST populate both tables, and that turns out to be mechanically very tricky.

Typically, one of the tables is different from the other and is perhaps dependent on the other in some way that isn't reciprocal. IF that is the case here, the dependent table must be the MANY side of a 1/Many and if you REALLY wanted that key to still be unique, there is a way. You make the relationship say 1/Many but then separately put a uniqueness index on the foreign key field in the dependent table. Then if you don't have a record in the dependent table, no problem. But if you do then it simultaneously has to satisfy the relationship AND the index constraint.

Here is the techie viewpoint on 1/1 relationships: In theory, a record's PK uniquely identifies the record in and of itself. That is, no other field in the table contributes to the uniqueness of the record. (If it did, your PK isn't really Prime.) If you have a 1/1 relationship, that means that the PK in your table also uniquely identifies ANOTHER record in a different table. In such cases, you should collapse the two tables together because they both depend on the same key. The concept of "purity of purpose" thus suggests that the PK should be in the same table as EVERYTHING that it identifies. But a split 1/1 pair of tables violates that concept. Note that there ARE exceptions to this concept and not everyone looks at it in exactly this way.
 
The title of this post really made me chuckle ... I'm just hoping the record isn't both the child AND the sister.
 
In a "One to one" relationship can the child record be left empty with the key...so the the record can be fill in at a later stage. What I have noticed it that at least one field of the child record must be filled out in order for the a actual record to be created in child table...
Can't test it right now, but I'm thinking that should be possible. Was that one field you're required to fill out the PK field? If so, that would make sense to me. Also, I am not sure I would call it a "child" table if the relationship is one-to-one. It's probably the correct term, but it just sounds funny to me.
 
Sure. When adding a record in the related table just do an outer join. When you add any field on the right side the foreign key will be created automatically.
 
The key is to include "Enforce Referential Integrity" for that 1/1 table relationship. Without RI, this shouldn't be a problem. WITH RI enabled, not so clean a problem.
 
But Pat, a 1/1 isn't unidirectional. It is bidirectional. We have had cases on this forum where because of the 1/1 relationship AND RI enabled, you end up in a situation where each side of the "1/1" forces the other side to exist first. We've had that here.

"Enforcing RI does not require a right side record to exist. It simply prevents one from existing if there is no left side row with the correct PK." Well, just turn that around because it is also 1/1 from the OTHER side of the relationship, too.
 
If you have a 1-1 relationship (and you really need it) THEN
when you create a master record you could create the child record with just the linking field set, and the rest of it blank - but there is no real need to do this at this stage.

The situation is just about the same whether you have an "empty" child record or no child record at all.
 

Users who are viewing this thread

Back
Top Bottom