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

[sethugec29]

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...

 

[arnelgp]

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)

 

[The_Doc_Man]

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.

 

[Isaac]

The title of this post really made me chuckle ... I'm just hoping the record isn't both the child AND the sister.

 

[The_Doc_Man]

@Isaac - see also the current thread on complex family trees.

 

[theDBguy]

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.

 

[MajP]

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_Doc_Man]

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.

 

[Pat Hartman]

its a no, you need to add some info on at least a field.

Just like 1-m, 1-1 Relationships allow 1-0 otherwise there would be an insurmountable logic problem that would prevent any row from being added. Typically, the "parent" side of the relationship has an autonumber PK. The -1 side has the normal long integer FK which is also defined as its PK.

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. Enforcing Cascade delete is dependent on your situation. If you allow unconditional deletes of the left-side, then cascade delete makes sense. If you don't want to delete left-side records if there is a matching right-side record, then do not specify Cascade Delete.

As the others have mentioned, true 1-1 relationships are rare in the real world.

 

[The_Doc_Man]

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.

 

[Pat Hartman]

Doc,
There was something else at play. The "master" table uses an autonumber as the PK. The "child" table uses a long integer as the FK and you mark the FK as the PK of the "child" table. Some people make the mistake of using autonumbers in both tables. That would require that both be created at the same time and would cause problems if the autonumbers ever get out of sync.

open the form and go to a new record. put something in one of the first two fields. close the form and look at the tables. You'll see a record in the master but no record in the child. Go back to the half finished record and add something in the other two fields close the form (or save it). look again at the tables and you'll see that the child has been created with the correct FK. The same thing should happen if the child table is a subform but if you try to add a second record, you'll get an error.

 

[sethugec29]

The title of this post really made me chuckle ... I'm just hoping the record isn't both the child AND the sister.

True lol I meant to write Parent instead of sister . Updated.

 

[gemma-the-husky]

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.

 

[Pat Hartman]

The only reason I joined the thread was because several people had said that 1-1 relationships REQUIRED both sides to be present and that is not true. So, yes you can always create the right side by simply populating the FK but if you don't populate any column, the right side will not immediately be created and that simply isn't a problem unless you don't understand that it isn't always going to be there.

 

[sethugec29]

Thanks everyone for the great input !