Database doubly connected relationship inserting problem

Question

I have two tables Plants and Information. For every plant there are many information, but for each plant there is a single MainInformation. So there is a one-to-many relationship and a one-to-one relationship between the two. The Information table has a PlantID and the Plants table has a MainInformationID. I want both fields in both tables not to be nulls. But now you can't insert either of the two records into their tables because each one requires their fields not be null, meaning they need the other record to be created first in order to create themselves. Perhaps this is not a good database design and something should be changed? (I am new to databases and entity framework)

I tried inserting into the database itself manually but I cant do it. I also tried this code with EntityFramework.

using (var context = new MyEntities())
        {
            var p = new Plant()
            {
                LatinName = "latinNameTest",
                LocalName = "localNameTest",
                CycleTime = 500
            };

            var i = new Information()
            {
                ShortDescription = "ShortDesc",
                LongDescription = "LongDesc"
            };

            p.MainInformation = i;
            i.Plant = p;

            context.AddToPlants(p);
            context.AddToInformation(i);

            context.SaveChanges();                
        }

Answer 1

You need to change the tables to allow for null. There is no other way to do this.

Answer 2

You may want to look at database transactions and how to use them with the Entity Framework. You can wrap both INSERTS into a single db transaction so the only results are both of them go in or neither go in.

Here is a link for transactions using EF. I didn't read through it but it seems to talk about them enough to get you started.

Answer 3

One of

• The 1-1 FK column has to be NULL
• The FK has to be disabled to allow parent insert before child
• You have a single dummy Information row that is used by default in FL column

SQL Server does not allow deferred constraint checking without "code change" rights so even wrapping in a transaction won't work

Sounds like an EAV schema, which has other problems