How to DELETE a record which has foreign keys - MySQL Java

Question

I have a table that looks like this

Car ID     Car Name     Part ID      Stock ID 
___________________________________________________
1          Audi          1             1

2          Benz          2             2

3          Corsa 1.3     3             3

4          Corsa 2.0     3             4

Now if I want to delete Corsa 1.3, it says Foreign key constraint Part ID. It can't delete it because of Part ID.

How do I fix this?

Cascade delete , Maybe this will help you http://stackoverflow.com/questions/129265/cascade-delete-just-once — Charaf JRA, Mar 27 '14 at 17:46
You need to delete the row that depends on "Corsa 1.3" first. — Jhuliano Moreno, Mar 27 '14 at 17:47

score 0 · Answer 1 · answered Mar 27 '14 at 17:48

0

You can't delete a recrod that refers to a foreign key; that's the whole point of them.

What you would need to do is either delete the record the key refers to or remove the constraint.

answered Mar 27 '14 at 17:48

Dave

4,546
2
38
59

score 0 · Answer 2 · answered Mar 27 '14 at 17:48

0

You should set the field that it's complaining to null, after that, the deletion will succeed. In your case before trying to delete corsa 1.3, set it's part id to null, thus removing the reference.

answered Mar 27 '14 at 17:48

olqmin

145
6

score 0 · Answer 3 · answered Mar 27 '14 at 17:50

0

Using DELETE CASCADE Option for Foreign Keys

answered Mar 27 '14 at 17:50

Charaf JRA

8,249
1
34
44

score 0 · Answer 4 · answered Mar 28 '14 at 21:40

The constraint is specified when creating the table which dictates how to deal with primary and foreign keys when updating and deleting, you can specify cascade on update and/or delete or not which is the problem your having

create table Orgs       (
    id bigint unsigned auto_increment,
    name varchar(100) not null,
    primary key (id),
    unique index name_ind (name)
) engine=InnoDB;

create table Households (
    id bigint unsigned,
    Orgid bigint unsigned,
    household varchar(20) not null,
    primary key (id),
    index org_ind (Orgid),
    foreign key (Orgid) references Orgs(id) on update cascade on delete cascade
)  engine=InnoDB;

How to DELETE a record which has foreign keys - MySQL Java

4 Answers4