1

Closed

NHinbernate Error: Foreign key ([..])) must have same number of columns as the referenced primary key (... [..., ...])

description

I'm receiving the following error when trying to run code generate for Fluent Mappings:
Foreign key (FKBE104CCFDC361233:table1 [table1_id])) must have same number of columns as the referenced primary key (table1_table2 [table1_id, table_id]).

the primary key on table1 is [table1_id]
the primary key for table2 is [table2_id]
I have a join table (table1_table2) between these, with a composite primary key.

I expect that the List property generated on table1 to table1_table2 be related with a composite foreign key.

I am not sure what the circumstances are, but here's the offending piece of code:

Method: WriteForeignKeyCollection
if (_table.IsSharedPrimaryKey(foreignKeyChild))
            {
                var fk = fks.First();
                if (fk.Columns.Count == 1)
                    _cb.AppendFormat("HasOne(x => x.{0}).Constrained();", childClass);
                //_cb.AppendFormat("References(x => x.{0}).Column(\"{1}\").ForeignKey(\"{2}\");",
                //      childClass, fkColumn, foreignKey.Name);
                //TODO composite keys
                return;
            }

            foreach (var fk in fks)
            {
                var sb = new StringBuilder();
                var propertyName = _codeWriterSettings.Namer.ForeignKeyCollectionName(_table.Name, foreignKeyChild, fk);
                var fkColumn = fk.Columns.FirstOrDefault();

                sb.AppendFormat(CultureInfo.InvariantCulture, "HasMany(x => x.{0})", propertyName);
                //defaults to x_id

                // KL: Only use .KeyColumn() if the foreign key is not composite
                if (fk.Columns.Count == 1)
                {
                    sb.AppendFormat(CultureInfo.InvariantCulture, ".KeyColumn(\"{0}\")", fkColumn);
                }
                // If composite key, generate .KeyColumns(...) with array of keys
                else
                {
                    var cols = fk.Columns.Select(x => string.Format("\"{0}\"", x)).ToArray();
                    sb.AppendFormat(CultureInfo.InvariantCulture, ".KeyColumns.Add(new string[] {{ {0} }})",
                                    String.Join(", ", cols));
                }
                sb.Append(".Inverse()");
                sb.AppendFormat(CultureInfo.InvariantCulture, ".ForeignKeyConstraintName(\"{0}\")", fk.Name);

                sb.Append(";");
                _cb.AppendLine(sb.ToString());
            }
In my scenario, IsSharedPrimaryKey is returning true. My foreign key accidentally gets generated with:
HasOne(x => x.{0}).Constrained();

Instead of writing out the full list of composite keys...

I'm not sure what is the purpose of the code inside IsSharedPrimaryKey.

Thanks in advance.
Closed Jun 25, 2013 at 6:04 PM by Martinjw
The shared primary key logic is extended to include TPT inheritance. It looks like it's all behaving correctly.

comments

Martinjw wrote May 8, 2013 at 5:43 AM

IsSharedPrimaryKey detects if all the primary key columns are also foreign key columns pointing to the same table.

Below, Table2's Pk1 and Pk2 are a joint primary key, and foreign keys to Table1 Pk1 and Pk2.
Table1.Pk1 <--------------- Table2.Pk1
Table1.Pk2 <--------------- Table2.Pk2

This "shared primary key" relationship is often used for inheritance. Eg Product - ProductDetail.
This is, in NHibernate terms, the HasOne / Constrained relationship.

In a standard many-to-many junction table this should not be true.
Table1.Pk1 <--------------- Table12.Table1Pk1
                                   Table12.Table2Pk1 ---------------> Table2.Pk1
For the junction table, Table12 here, the primary key columns are all foreign key columns, just like the "shared primary key" above. But there are 2 foreign keys pointing in different directions. IsSharedPrimaryKey detects that, and returns false.

By the way, the NHibernate mapping creates an entity for the junction table. You may want an implicit many-to-many relationship, but you'll have to rewrite that manually (see the best practices in http://nhforge.org/doc/nh/en/index.html#best-practices "Don't use exotic association mappings.").

What I suspect has gone wrong is that your database is missing one of those foreign key constraints. Like this:
Table1.Pk1 <--------------- Table12.Table1Pk1
                                   Table12.Table2Pk1                       Table2.Pk1
This looks like "shared primary key". There are legitimate reasons not to include a foreign key constraint- perhaps you are using partitioning, or the data is known to be inconsistent. Or maybe someone forgot to add the constraint. It's impossible to reverse engineer those decisions, and the generation can only use what it's got.

Lots of databases have these inconsistencies, which is why the database schema model can be changed (and "fixed"!) after it is loaded from the database and before you run code generation. You have to know what the inconsistencies are, and use the programmatic model, but it should be easy to work those out.

If this doesn't look correct to you, can you send me the database DDL so I can check?