The ORM Foundation

Create a simple schema where ALL roles are optional and expected uniqueness constraints apply.

Person(name) has Age()
Person(name) is married to Person(name)

The DDL creates a foreign key from spouse to spouse. Because all roles are optional, I'm not sure the foreign key should be created. If I have recorded Bob being age 25 and then want to indicate that Bob is married to Mary, I shouldn't have to add a new entry to the Person table for Mary.

Similarly, create a simple schema where all roles are optional and expected uniqueness constraints apply.

Person(name) has Age()
Person(name) likes IceCream(type)

The DLL creates a foreign key from PersonLikesIceCream to Person. Because all roles are optional, I'm not sure the foreign key should be created. If I add the fact that Bob (a new person) likes Chocolate, why do I also need to put a new entry for Bob in the Person table?

Implementing it the way you have makes it easier to run reports that enumerate Person because all Person are identified in one table. But this is only a benefit when there is at least one functional fact role. In the schema below, there is no Person table, so it is more difficult to enumerate all Person.

Person(name) likes IceCream(type)
Person(name) speaks Language(name)

Suppose this schema:

Person(automatic id) has Name()
Person(automatic id) likes IceCream(type)

If you enter a new Person 'Bob' who gets an automatic id of 10, and then you want to specify that Bob likes Chocolate, you can't do this with some databases because you can't enter the specific number 10 into the automatic id field of the ice cream table. So in this case, it helps to create the foreign key constraint even though all roles are optional.

SO...I'd like to understand better the thinking behind when foreign key constraints are put in the DDL.

I still think there is a problem here with the recent release.

It is my understanding that there is an implied subset constraint from every role an object plays to every mandatory role that same object plays. In some cases these implied subset constraints can be ignored when mapped to a SQL database because the roles map to the same table and column; this typically happens for functional roles played by the same object type. But when the subset constraint must be enforced, it is typically implemented via foreign key constraint. The DDL generation for SQL Server seems to be creating foreign keys where they aren't needed.

In this example, suppose all the roles are optional:

Person(name) is managed by Person(name)
Person(name) is of Age()

The SQL Server DDL has a foreign key from employee to manager. This means I can't specify that "Bob is managed by John" without first adding an entry to the Person table for John, and this seems to be an unnecessary complication.

In this example, suppose all the roles are optional:

Person(name) is of Age()
Person(name) likes IceCream(flavor)

Again, the DDL creates a foreign key from the second fact to the first. So I can't record the fact that Bob likes Vanilla without first adding an entry to the Person table for Bob. Another, possibly related problem, is that I am able to first add Bob to the Person table without a corresponding Age, which is not correct because Person is not independent.

Sorry for the delayed reply. As you noted, our current generation process is not accounting for these situations correctly. We have identified what the problem is and how to address it, and we are in the process of doing so as part of some larger overall changes to our generation process. Once the fixes for these issues are in place, I will post the latest versions of the files up here for people to download. At that time, you should be able to save them over the top of the files installed by the current release without any issues.