How to delete foreign key record

Question

I've got two tables Employee and Address. Employee is my main table and Address is a child table associated to Employee via the foreign key AddressId.

The records from Address are not deleted when I delete an Employee record. How I can rewrite my code to do so?

Employee:
  [Id](Primary Key)
  [FirstName] 
  [LastName]
  [Email]
  [AddressId] (Foreign Key -> Address.Id)
  [Code]

Address:
  [Id] (Primary Key)
  [Details]
  [State]
  [Country]

Here is my current code:

public bool DeleteEmployee(int id)
{
            using (var context=new EmployeeDBEntities())
            {
                var employee = context.Employee.FirstOrDefault(x => x.Id == id);
               
                if (employee != null)
                {
                    context.Employee.Remove(employee);
                    context.SaveChanges();
                    return true;
                }
                return false;
            }
        }
    

Answer 1

You're looking for the ON DELETE CASCADE feature, which will indicates to MySQL that a record should be deleted when its "parent" record (in another table) is deleted.

Something like this:

CREATE TABLE address (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Details VARCHAR(255) NOT NULL,
    State VARCHAR(255) NOT NULL,
    Country VARCHAR(255) NOT NULL
);

CREATE TABLE employee (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    AddressId INT NOT NULL,
    Code VARCHAR(255) NOT NULL,
    FOREIGN KEY (AddressId)
        REFERENCES address (Id)
        ON DELETE CASCADE
);