referntial integrity issue with two tables cant delete a record

Question

So I am trying to delete a record from customers table. I would assume it's a referential integrity issue, however I don't really see what is wrong with it. Could anyone shed some light? Please and Thanks. Error Message below

 DELETE FROM customers
        WHERE customer_id = 1

table structures:

    CREATE TABLE  payment_details  (
    paytype_id varchar(10),
   card_no  int(8) NOT NULL,
   customer_id  int(50)  NOT NULL,
   CVV  int(3) NOT NULL,
   card_type  ENUM ('Visa', 'Amex', 'American Express', 'Mastercard') NOT NULL,
   expiry_date  DATE NOT NULL,
   PRIMARY KEY(paytype_id),
   KEY customer_idfk1 (customer_id),
   CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE  customers  (
   customer_id  int(50)  NOT NULL AUTO_INCREMENT,
   fname  varchar(20) NOT NULL,
   lname  varchar(20) NOT NULL,
   dateOfBirth date NOT NULL,
   address  varchar(30) NOT NULL,
   city  varchar(20) NOT NULL,
   county  ENUM('Armagh','Carlow','Cavan','Clare','Cork','Derry','Donegal','Down','Dublin','Fermanagh','Galway','Kerry','Kildare','Kilkenny','Laois','Leitrim','Limerick','Longford','Louth','Mayo','Meath','Monaghan','Offaly','Roscommon','Sligo','Tipperary','Tyrone','Waterford','Westmeath','Wexford','Wicklow') NOT NULL,
   phone  int(10) NOT NULL,
   email  varchar(20) NOT NULL,
   payment_method  ENUM ('Visa', 'Amex', 'Cash', 'American Express', 'Mastercard') NOT NULL,
   valid_licence varchar(5) NOT NULL,
   status  varchar(10),
   PRIMARY KEY (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

error - #1451 - Cannot delete or update a parent row: a foreign key constraint fails (g00337857abu.payment_details, CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id) REFERENCES customers (customer_id))

Answer 1

You have a foreign key from payment details to customers.

This is a declarative referential integrity constraint which enforces that you can’t have a row in payment details (‘child’ row) for which the customer is is not in customers (‘parent’).

The idea is to maintain data consistency - if you had an ‘orphan’ child row without a ‘parent’ Customer, you would never know which customer made the payment. If you want to delete the customer and his payments, you can either delete the payments first:

DELETE FROM payment_details where customer_id = 1

Or you can define the foreign key as a cascading one in which case the engine will delete it for you:

CONSTRAINT customer_idfk1 FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id) 
    ON DELETE CASCADE