Why can't SQL Server optimizer use foreign key
I can't reproduce your problem, which means there's likely something missing in your description (or I've missed something in your description!).  If you want more detailed help with this issue, please suggest changes to this repro code that allow it to more closely match your situation.

Here's an attempt and recreating the tables and data based on your description:


  There is an index on ToBeTransferredTemp.type
  and unique index on CODES.idstring.
  There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring


In addition to those three pieces of information, I noticed that there is a clustered index on each of the two tables.

USE [master];
GO
DROP DATABASE IF EXISTS [245776];
CREATE DATABASE [245776];
GO

USE [245776];
GO

CREATE TABLE dbo.CODES
(
    IDSTRING varchar(100),
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_CODES PRIMARY KEY (IDSTRING)
);

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.Codes (IDSTRING);

CREATE TABLE dbo.ToBeTransferredTemp 
(
    ID varchar(100),
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type]) 
        REFERENCES dbo.CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.ToBeTransferredTemp ([type]);


Here are the two tables, with clustered primary keys, foreign key constraint, an NC index, and a unique index.

I have included some filler columns in order for the original key lookup plan shape to be possible.

INSERT INTO dbo.CODES
SELECT TOP (8500) 
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    v1.number
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.ToBeTransferredTemp
SELECT 
    CAST(NEWID() AS varchar(100)),
    c.IDSTRING,
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
FROM dbo.CODES c;


This loads exactly 8500 rows into each table.

The query as written in the OP produces a merge join plan for me:

SELECT *
FROM ToBeTransferredTemp a 
    INNER JOIN codes c
        ON a.[type] = c.idstring;




In order to get the plan shape from the OP, I have to hint the join type (INNER LOOP JOIN) and hint that the CODES table should be accessed by the unique index.

SELECT *
FROM ToBeTransferredTemp a 
    INNER LOOP JOIN codes c WITH (INDEX (UQ_Codes_IDSTRING)) 
        ON a.[type] = c.idstring;




This appears to be the right plan shape.  However, the estimates are exactly right in this plan.