none
EF 4.1 fluent mapping HasOptional() and Enforce Foreign Key Constraint RRS feed

  • Question

  • Hi,

    I am exploring the possibilities of EF 4.1 code first approach, and I have a question regarding defining relationship when creating new database (Sql Server 2008 Express edition).

    This is a 0-1..many scenario, so I need FK to accept NULL values. Using fluent mapping and EntityTypeConfiguration:

    HasOptional(x=>x.A).HasMany(x=>x.B).Map(x=>x.MapKey("A_Id"));

    This one creates correct relationship, and A_Id is set to allow NULL values. However, Enforce Foreign Key Constraint is set to Yes, which prevents entering NULL values to A_Id. Why is this happening, shouldn't it works like this:

    HasOptional(): set FK to allow nulls, Enforce Foreign Key Constraint = No

    HasOptional(): set FK to deny nulls, Enforce Foreign Key Constraint = Yes

     

    How to have A_Id allow NULLs, while at the same time setting Enforce Foreign Key Constraint to false?

    Thank,

    Goran


    • Edited by Goran _ Wednesday, October 12, 2011 9:51 AM
    Wednesday, October 12, 2011 9:49 AM

Answers

  • Hi, Goran.

    I think you understand it incorrectly.

    In your example, the "A_Id" column DOES allow to have NULL values, regardless of if it has "Enforce Foreign Key Constraint" set to true/false.

    Databases use "Enforce Foreign Key Constraint" configuration flag to ensure that data in a relationship will stay consistent.

    Using HasOptional() or HasRequired() configuration methods will affect only on if a FK column is nullable.

    If you want to change the way EF constructs relationship, you can run your hand-made sql after the Database creation script are run.

     

    Regards

    • Marked as answer by Goran _ Friday, October 14, 2011 11:01 AM
    Wednesday, October 12, 2011 6:16 PM

All replies

  • Hi, Goran.

    I think you understand it incorrectly.

    In your example, the "A_Id" column DOES allow to have NULL values, regardless of if it has "Enforce Foreign Key Constraint" set to true/false.

    Databases use "Enforce Foreign Key Constraint" configuration flag to ensure that data in a relationship will stay consistent.

    Using HasOptional() or HasRequired() configuration methods will affect only on if a FK column is nullable.

    If you want to change the way EF constructs relationship, you can run your hand-made sql after the Database creation script are run.

     

    Regards

    • Marked as answer by Goran _ Friday, October 14, 2011 11:01 AM
    Wednesday, October 12, 2011 6:16 PM
  • Yes, you are correct. I have just tried it and it will allow null value even if enforce foreign key constraint is set to Yes. My confusion came when trying to copy some data from another database, and it complained about missing reference, and when I set Enforce foreign key constraint to No, I was able to upload data. After copying I tried to enforce FK constraint to true, and had no complains about data integrity.

    Thanks.

    Friday, October 14, 2011 11:01 AM