[SalesForce] INVALID_FIELD, Foreign key external ID not found – Inserting Related Records via External Id

What exactly happens behind the scenes,when we say

Account acc=new Account(Name='Blog Acc 901', Master_Id__c='Blog Acc 9001');
Contact cont=new Contact(FirstName='Bob901', LastName='Buzzard', Account=new Account(Master_Id__c='Blog Acc 9001'));

We are specifying three objects to be created in memory. The first is an account (acc), the second is a contact (cont), and the third is another account (cont.account).

...

*-what exactly is getting saved in Account foreign key reference field in Contact? is it the Database memory location for that record?

It's a normal memory reference to a heap location. Every object occupies memory on the heap, and so therefore each usage of the word new creates a new object on the heap. Imagine the heap looks like this after running those three lines of code:

Address Class           Data
0x0001  Schema.Account  Name='Blog Acc 901', Master_Id__c='Blog Acc 901'
0x0002  Schema.Account  Master_Id__c='Blog Acc 901'
0x0003  Schema.Contact  FirstName='Bob901', LastName='Buzzard', Account=0x0003

*In this particular eg: when we say Account = new Account(Master_ID__C = 'abcd') ,what exactly is happening in heap memory and at what point does it realise it is pointing to acc

Let's be clear: Cont.Account (0x0002) is not acc (0x0001), and the two references never point to the same heap address, even after inserting the new records. You can prove this by checking that System.assert(acc!==cont.account).

At no point in the data execution (in this scenario) does Apex Code "realize" that cont.Account is pointing to acc, because Apex Code considers them two discrete memory objects, even if they are literally "pointing" to the same database record.

The database itself knows that an External ID can be used to reference a record manually without an ID, and so when it encounters the Master_ID__c value, it is intelligent enough to consult the External ID index and acquire the appropriate ID for the given record.

*Is external id,the only type of field that can be specified in this scenario?

You can specify any field you want to, but only the External ID value will be considered for determining the correct record ID to use. This is useful for certain kinds of constructs where you want to be able to reference the related object later without a map. Here's an example trigger that utilizes this method:

trigger createSalesOpp on Order__c (before insert, after insert) {
    Opportunity[] newOpps = new Opportunity[0];
    if(Trigger.isBefore) {
        for(Order__c record:Trigger.new) {
            record.Opportunity__r = new Opportunity(AccountId=record.Account__c, StageName=Info.DefaultOpportunityStage, CloseDate=Info.DefaultCloseDate, Name='Holding Opp');
            newOpps.add(record.Opportunity__r);
        }
        insert newOpps;
        for(Order__c record:Trigger.new) {
            record.Opportunity__c = record.Opportunity__r.Id;
        }
    } else {
        for(Order__c record:Trigger.new) {
            newOpps.add(new Opportunity(Id=record.Opportunity__c,Name='Order '+record.Name));
        }
        update newOpps;
    }
}

We are creating a new order, either from the UI or data loader. We want to report to the funnel, so we backfill an opportunity. We do this in four steps:

1. Create records in memory for each opportunity.
2. Insert the opportunities.
3. Copy the successful ID values back to the order.
4. Rename the opportunity to match the order name.

Steps 1-3 occur in the before insert event, so Trigger.newMap has no keys, because the records have not yet been assigned an ID. In this case, we use the relationship as a storage device to temporarily associate the opportunity to the order long enough for us to create the record.

Step 4 occurs in the after insert event, so we now know what the ID for each Order is, and its auto-number value. We didn't know this initially, so we couldn't have specified this when creating the opportunity, but we wanted to store the data back in the order, which we couldn't have done in the after insert event, because the records would already be read-only.

In this way, you can use the mechanism as temporary storage, but you must remember to save the data somewhere permanent if you need to know this data later, because the association would disappear when the trigger ends.

You can't specify both an ID (in this case, null), and an external ID (which presumably would generate a non-null value). Do not specify both Parent_Id__c and Parent_Id__r on the same record.