1

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))

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.