Avoid Circular Dependancy: MySQL/Entity Framework

We're having trouble dealing with a circular dependancy and wondered if anyone could suggest a solution. If we want to delete a client, ent开发者_运维知识库ity framework refuses to do this because it tells us a foreign key constraint fails. Our tables are setup like this:

ClientAccounts

Id [PK]
Forenames
Surname
DefaultEmailId [FK, NULLABLE]

ClientEmailAddresses

Id [PK]
Description
EmailAddress
ClientId [FK, NON-NULLABLE]

So a Client can have zero or many email addresses associated with their account. One of which will be their default contact email address.

I recognise that if we allowed the ClientEmailAddresses table to have a null ClientId, it would work ok; but we don't want a situation where we could have orphan email records.

Maybe it's better to store field 'default' in table ClientEmailAddresses and remove DefaultEmailId from table ClientAccounts. I don't know your code structure but sometimes structure like yours is redundant.

Shouldn't you first delete the entries in ClientEmailAddresses ?

Imagine a stored procedure like this :

##  delete Client
delete from ClientEmailAddresses where ClientId = ID2delete
delete from ClientAccounts where Id = ID2delete

where Id2delete is the client ID to delete.

I haven't try, but should work !