How can I create a mandatory, non-identifying relationship between two entities?

Question

I'm working in MS Visio 2010. This is the relevant part of my ERD:

The relationship between Event and Adventure is correct: there's a foreign key from Event to Adventure, and that FK is part of Event's primary key.

However, what I can't figure out is how to make the relationship line from Adventure to AccomodationType be the same as the one from Event to Adventure, without making that relationship part of the PK of adventure. When I look at the 'Miscellaneous' properties of that relationship line, I want it to be:

• Cardinality: Zero or more
• Relationship type: Non-identifying
• Child has parent: Not optional (mandatory)

But the checkbox for the third property is greyed out, and toggles between True/False as I make the relationship Non-identifying/Identifying.

The only way I could figure out was to disconnect the two columns, from the 'Definition' tab, which then un-grey's the 'Optional' checkbox, but then I lose the foreign key property on the accomType column, and while the relationship symbols are correct, the line remains dotted.

How can I make the cardinality of the line on the left appear the same as the relationship line on the right, without adding accomType to the PK of Adventure?

Solution

This MS TechNet thread identifies the problem as having to do with the way that Visio 2010 automatically inserts the foreign key when you create a relationship between entities:

It indeed seems that typing the foreign key field name first and then establishing the relationship makes this possible. Checking/unchecking Req'd on the foreign key then automatically checks/unchecks Optional on the relationship.

There are two ways to solve this problem, depending on whether you are creating a new relationship on your diagram or trying to fix an existing one.

How to create a mandatory, non-identifying relationship

Before creating the relationship, add the desired FK attribute(s) to the child entity, using the same name(s) as in the PK of the parent entity. Make sure the "Req'd" option is checked for each FK attribute.

In your example, we add a "name" attribute to the Adventure entity:

Now adding the relationship automatically creates the FK but preserves the non-identifying, non-optional state of the existing attribute on Adventure:

You are free to rename the FK attribute however you like at this point.

Note that the "Child has parent: Optional" check-box is still grayed out. Even after this point, changing the properties of the relationship and/or the attributes involved sometimes have undesired side effects, and you may find the wrong crow's foot symbol getting "stuck" again. Fortunately, there's a quicker way to fix this than deleting the relationship and starting over.

How to fix an existing relationship

Select the relationship with the "stuck" crow's foot symbol and go to the Definition tab in the Database Properties window.

You will see a line with arrows connecting the PK and FK attributes in the parent and child entities. Select the connected attributes in both tables and disconnect them by clicking the button in the middle column. If the key is compound, you'll see multiple pairs of attributes:

Once you've disconnected all the attributes involved in the relationship, the "Child has parent: Optional" box in the Miscellaneous tab will become active again. Set the properties of the relationship to be Non-identifying with Optional parent, double check that the FK attributes in the child entity are set to "Req'd" and then go back to the Definition tab and re-associate the PK and FK attributes: