Cannot delete or update a parent row: a foreign key constraint fails Error

Question

i am trying to delete the table appusers, with the following command:

drop table appusers;

and i get the following error:

Cannot delete or update a parent row: a foreign key constraint fails

this is the scheme for my tables.

CREATE TABLE appUsers (
  uid INT PRIMARY KEY AUTO_INCREMENT,
  fullName VARCHAR(80) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(100) NOT NULL,
  Gender VARCHAR(7) NOT NULL,  /*["Male", "Female"]*/
  Country VARCHAR(150) NOT NULL,
  Bdate date NOT NULL, 
  Status VARCHAR(25) NOT NULL, /*["Single", "In Relationship", "Merried", "Divorced", "Widow"]*/  /*check married!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
  Religion VARCHAR(25) NOT NULL, /*["Jewish", "Christian", "Muslim", "Atheist", "Other"]*/
  ReligionStrength INT(1) NOT NULL, /*0-5*/
  PoliticalView VARCHAR(25) NOT NULL, /*["Left Wing","Center","Right Wing"]*/
  Occupation VARCHAR(25) NOT NULL, /*["Unemployed","White Collar","Blue Collar","Student", "Independent"]*/
  Volunteering VARCHAR(25) NOT NULL,  /*["Yes", "No"]*/
  Donating VARCHAR(25) NOT NULL,  /*["Yes", "No"]*/
  Economy VARCHAR(25) NOT NULL, /*["Poor","Middle Class","Rich"]*/
  EducationalYears INT(2) NOT NULL
);

and I have the following table

CREATE TABLE Accelerometer(
  id INT PRIMARY KEY AUTO_INCREMENT,
  uid INT NOT NULL,
  sampleTime timestamp(2) NOT NULL,
  data VARCHAR(100) NOT NULL,
  FOREIGN KEY (uid) REFERENCES appUsers(uid) ON DELETE CASCADE 
);

as far as I know, if I delete the table appusers, the Accelerometer table should be deleted too, what i am missing here?

If you delete **a row** of table `appusers` then *all related rows* of `Accelerometer` are also deleted. Not if you drop the table. — Giorgos Betsos, Mar 10 '16 at 13:41
First delete the tables relaying on this table, only then delete it. — sagi, Mar 10 '16 at 13:43
@GiorgosBetsos, and if i want to delete the whole table, what can i do? — Dan The Man, Mar 10 '16 at 13:49

score 2 · Accepted Answer · answered Mar 10 '16 at 13:50

2

ON DELETE CASCADEonly apply to data, not metadata.

=>

 Alter TABLE Accelerometer drop FOREIGN KEY (uid);

Then only

drop table appusers;

answered Mar 10 '16 at 13:50

J. Chomel

8,193
15
41
69

score 0 · Answer 2 · edited May 09 '17 at 11:05

I realise this is stale for a while and an answer had been selected, but how about the alternative to allow the foreign key to be NULL and then choose ON DELETE SET NULL.

The following might work nicely for you:

ALTER TABLE 'Accelerometer' 
DROP FOREIGN KEY 'uid_fk';

ALTER TABLE 'Accelerometer'
ADD CONSTRAINT 'uid_fk' FOREIGN KEY ('uid') REFERENCES 'appUsers' ('uid') ON 
UPDATE CASCADE ON DELETE SET NULL;`

Personally I would recommend using both ON UPDATE CASCADE as well as ON DELETE SET NULL to avoid unnecessary complications, however your set up may dictate a different approach.

Hope this helps.

Cannot delete or update a parent row: a foreign key constraint fails Error

2 Answers2