Foreign Key Not Populating with Primary Key Values

Foreign Key Not Populating with Primary Key Values

The foreign keys will not auto-populate, as it doesn't know what foreign key to use. You need to either insert the rows into the JobTitle_tbl table, then select the IDs back out (or use @@identity if using sql server)

select id from JobTitle_tbl where Job_title = ''

Another option would be to update your insert statements to include the primary key, although you'll have to allow identity inserts first.

SET IDENTITY_INSERT JobTitle_tbl ON
into the JobTitle_tbl (id, title) values (1, 'Manager')
SET IDENTITY_INSERT JobTitle_tbl OFF

In either case, you'll need to then update your first insert statements with the ID that you have.

insert into Employee_tbl (LastName, FirstName, JobID) values ('Smith', 'John', 1)

Foreign keys not populated

Your thinking is wrong.

There is no magic that populates the foreign key columns.

When those columns were added, they were assigned NULL value (because of the DEFAULT NULL clause of the column definitions.)

With InnoDB, a foreign key constraint disallows the assignment of invalid values to the foreign key column. The foreign key constraint on prof_id (in my_contacts table) says that any value assigned to that column must appear as a value in the prof_id column of at least one row in the profession table.

The foreign key (as defined as shown in the question), defaults to ON UPDATE NO ACTION ON DELETE NO ACTION.

And the default NO ACTION is equivalent to specifying RESTRICT. That means that if there are any rows in my_contacts table with values of prof_id that reference a row in profession table, then an attempt to DELETE the referenced row from the profession table will cause an error. Also, any attempt to change to the value of the prof_id column of the referenced row in the profession will cause an error. Those actions would violate the rules enforced by the foreign key constraint.

---

The relationship between rows is established by storing a common value in the prof_id column of the two tables. Storing a non-null value in the prof_id column is what relates the row in my_contacts to a row in profession.

The foreign key constraint prevents invalid values from being assigned, and prevents changes to rows in the parent profession table that would cause established relationships to be broken.

The foreign key constraint does not cause any automatic assignment of values to replace NULL values in the prof_id column.

Context not populating foreign key on adding new entities to database

I think the problem is that you use your newItem as reference in the newScan before you have saved the newItem. So the newScan doesn't get an actual DB entity id to populate, only an in-memory copy that needs saving first.

Try it this way - (PS: I haven't tested this, just writing what I believe can fix it)

if (ModelState.IsValid)
{
    var newItem = new Item()
    {
        Name = scan.Name,
        Upc = scan.UPC,
        DateCreated = DateTime.Now,
    };

    _context.Item.Add(newItem);
    await _context.SaveChangesAsync();

    var newScan = new Scan()
    {
        Item = newItem, // after EF has saved the Item entity, it should retrieve the DB generated Item automatically
        CreateDate = DateTime.Now,
        ExpirationDate = scan.ExpirationDate,
    };

    newItem.Scan = new List<Scan>() { newScan };


    _context.Entry(newItem).State = EntityState.Modified;
    _context.Entry(newScan).State = EntityState.Added; // Maybe you should also save this before updating the newItem a second time. It may throw an error if not!
    await _context.SaveChangesAsync();    
    
}

Can't insert data for 2 foreign key for the same primary key in SQL Server

Constrainsts are created to prevent corrupting data by inserting or updating your tables.

In your case, you are trying to insert data which is not existing. You are trying to insert into Currency_rate values ID's of Currency which are not existing in Currency table. So this is a whole goal of constraints - prevent corruption of data.

Just for demonstration purposes I've created Country table:

Create table Country (
  ID int primary key identity(1,1),
  CountryName nvarchar(50)
)

Then your first step would be:

INSERT INTO dbo.Country
(
    --ID - this column value is auto-generated
    CountryName
)
VALUES
(
    -- ID - int
    N'India' -- CountryName - nvarchar
)
, (N'Canada')
, (N'South America')

The second step will be:

INSERT INTO dbo.Currency
(
    --ID - this column value is auto-generated
    Code,
    Name,
    Is_Active,
    Is_Base_Currency,
    Country_id
)
VALUES
(
    -- ID - int
    N'Code1', -- Code - nvarchar
    N'India Currency', -- Name - nvarchar
    0, -- Is_Active - bit
    0, -- Is_Base_Currency - bit
    1 -- Country_id - int
)
, (
 N'Code2', -- Code - nvarchar
    N'Canada Currency', -- Name - nvarchar
    0, -- Is_Active - bit
    0, -- Is_Base_Currency - bit
    2 -- Country_id - int
)
, (
 N'Code3', -- Code - nvarchar
    N'South America Currency', -- Name - nvarchar
    0, -- Is_Active - bit
    0, -- Is_Base_Currency - bit
    3 -- Country_id - int
)

And the final step is the following:

Insert into Currency_rate(Currency_id,Base_currency_id,Rate,Ts)
values(1,1,121212.212121,'2008-11-11 13:23:44.111'),
(2,2,232323.323232,'2009-11-11 13:23:44.222'),
(3,3,343434.434343,'2010-11-11 13:23:44.333')

---

Related Topics