Search code examples
mysqlsqlphpmyadmininnodb

Deleting tables on a relational database (InnoDB)

I managed a relational database and I want to delete a table and their sons (reference as a reference value like fk_table_value).

When I'm doing a simple "DELETE FROM companies WHERE id=4" I have that error message : "Cannot delete or update a parent row: a foreign key constraint fails".

Here you can see my database map.

database map

Solution 
  • CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

    Lets say you have defined two tables like this. So we can see that rooms have a foreign key on buildings and it is set ON DELETE CASCADE. That means that if entity from buildings is deleted all the room with that building_no will be also deleted.

    This will also fix your problem.