Toad World® Forums

0..1 to 0..n relationship in mysql


#1

When creating this kind of relationship in mysql, foreign key contraint is generated, forcing relationship

0…1 to 0…n

into

1…1 to 0…n

The foreign key is wrong in this situation. Mysql does not have anythng better for it though.

And when using MyISAM (which is infact as default mysql database type /should be InnoDB, or that new one…) foreign keys are useless completely.


#2

Hello arki,

I’m afraid, we do not understand well what you mean.

When I create a relationship, see the Relationship properties, Cardinality area-> Mandatory Parent is selected automatically therefore you can see:
1…1 to 0…n

Identifying relationship:
Atribute in child entity is set as Not Null, Mandatory Parent is greyed and cannot be changed.

Non-identifying relationship:
You can change the cardinality.
To have it:
0…1 to 0…n,
clear the Mandatory Parent checkbox.

The foreign key is wrong in this situation. Mysql does not have anythng better for it though.
Sorry, we do not understand at all.

Thanks in advance for more details.

Regards,

Vladka + Mario


#3

We have put our heads together with our testers :wink: and now can possibly see what you mean:

Relationship cardinality in your database is:
0…1 to 0…n.

After reverse engineering to TDM3, it is:
1…1 to 0…n.

Reason:
During RE, there’s no way how to find out what the cardinality in your db is. 1…1 to 0…n is loaded by default.
Anyway, it is logical information that does not influence the model in fact and final DDL script either.
If you need to have it 0…1 to 0…n in TDM3, please clear the Mandatory Parent checkbox.

If you have any questions, please write us back. Thanks.

Vladka + TDM Team


#4

Maybe I do not understand the relationship.
I thought that 0…1 to 0…n mens that a value on the right side (child side, which could be multiple times there) doesn’t have to be on the left side (parent side).

If that’s correct and I generate DDL script for it (mysql 5), it generates foreign key constraint for the right side.

And the contstraint in fact changes the relationship into 1…1 to 1…n. The parent is mandatory because of that constraint (if not null). There’s no reason to have the constraint there when values aren’t needed to be checked. In fact the relationship is purely informational. Therefore my description of 0…1 must be wrong.
But if I am right, the relationship could change to informational automatically.

And reverse engineering of created tables approves that MysQL supports only mandatory foreign keys.

I see now that if I want to maintain relationship, the only value on right that don’t have to be on left side is NULL (nonidentifying relationship, NOTNULL attribute unchecked).

Sorry for excessive work.


#5

Hi,

Here I’m with a few remarks on this issue:

What does the cardinality mean and show?
Cardinality means that values in child attributes can be null. (If they are not null, these values have to exist in parent attributes.)
At the same time, cardinality says that value of parent attribute can occur in child attribute more times.


Info on Mandatory Parent:

“0…1 to 0…n” means cardinality “zero-or-one to zero-or-more”.
If this cardinality is set, child attributes should be NULL.

If Mandatory Parent checkbox is selected, the cardinality will change to “1…1 to 0…n” and child attributes should be NOT NULL.

-> This synchronization does not work at the moment and you need to change the NULL and NOT NULL values of child attributes manually. However, it will be fixed in any future version and the synchronization between Parent Mandatory and NULL/NOT NULL value in child attributes will be automatic.

If Mandatory Parent checkbox is selected, the relationship says that the child entity is dependent on parent entity (that it is dependent on existence of the parent entity).

If Mandatory Parent checkbox is clear, the relationship says that child entity can exist even without its parent entity.

Cardinality does not determine whether foreign key constraint will be generated for right or left side. Foreign key is always created in child table (where relationship leads).

I believe this information makes this issue much clearer.

Regards,

Vladka + Mario