EF6: Composite Primary Key Field as Foreign Key (ALTER TABLE statement conflicted with the FOREIGN KEY constraint)

Question

I'm trying to use one of the fields in a composite primary key as a foreign key linking to another entity.

public class Security
{
    [Key]
    [MaxLength( 20 )]
    public string Symbol { get; set; }
}

public class Price
{
    [Key, Column( Order = 1 )]
    [MaxLength( 20 )]
    [Required]
    public virtual string Symbol { get; set; }

    [Key, Column( Order = 2 )]
    public DateTime AsOf { get; set; }

    [ForeignKey("Symbol")]
    public virtual Security Security { get; set; }
}

When I do add-migration/update-database I get the following error message:

ALTER TABLE [dbo].[Prices] 
ADD CONSTRAINT [FK_dbo.Prices_dbo.Securities_Symbol] 
FOREIGN KEY ([Symbol]) 
REFERENCES [dbo].[Securities] ([Symbol]) 
ON DELETE CASCADE 

System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Prices_dbo.Securities_Symbol". The conflict occurred in database "FitchTrust", table "dbo.Securities", column 'Symbol'.

FYI, I added the Security property to Price after initially creating Price. Don't know if that maybe contributed to the problem.

Is it possible to use one field in a composite primary key as a foreign key as well? If so, how do I configure things (preferably by annotations, but fluent API is okay, too).

Answer 1

This is a Sql Error, and it relates to existing data. It means that Referential Integrity introduced by the new Foreign Key is already violated, i.e. there is at least one value of Symbol in dbo.Prices.Symbol which doesn't exist in dbo.Securities.Symbol. Find the culprit(s) and either add the row to dbo.Securities, or delete it from dbo.Prices

Find:

SELECT * 
FROM dbo.Prices p
WHERE NOT EXISTS
(SELECT 1 
 FROM dbo.Securities s
 WHERE s.Symbol = p.Symbol);

Insert into Securities (Likely there will be other columns you will need to source elsewhere)

INSERT INTO dbo.Securities(Symbol)
  SELECT p.Symbol
  FROM dbo.Prices p
  WHERE NOT EXISTS
    (SELECT 1 
     FROM dbo.Securities s
     WHERE s.Symbol = p.Symbol);

Or Delete from Prices:

DELETE
FROM dbo.Prices p
WHERE NOT EXISTS
(SELECT 1 
 FROM dbo.Securities s
 WHERE s.Symbol = p.Symbol);