MySQL (MariaDB)

SQL ERROR -532 THE RELATIONSHIP CONSTRAINT-NAME RESTRICTS THE DELETION OF ROW WITH RID X’RID-NUMBER’

Photo of author

sood

sqlcode=-532, sqlstate=23504

This article will explain the cause and solution to DB2 SQL Error-532 or sqlcode=-532, sqlstate=23504, which is also similar to MySQL : ERROR 1216: Cannot add or update a child row and SQL Server error: foreign key constraint failed

Foreign Key Constraint Error Possible Manifestation

The DELETE statement cannot be executed. The contents of the object table are unchanged.

These types of error occur when SQL command tries to Delete Row from Parent Table which is having Foreign Key reference with other table.

Reason for the Error

When Delete command gets executed it will find first relationship of parent table with its foreign key referenced child table, if any entry is available for that foreign key value then it will not allow to perform Delete operation.

A row of the Parent table cannot be deleted because it has a dependent in a relationship with a delete rule of RESTRICT or NO ACTION.

CREATE TABLE Department –- Parent Table
 (Department_num INT PRIMARY KEY,
 Department_Name CHAR(20));

CREATE TABLE Faculty –- Child Table
 (Faculty_num INT,
 Faculty_Name CHAR(20),
 FOREIGN KEY (Faculty_num) REFERENCES Department_num
 ON DELETE CASCADE)

Consider above table with constraints specified between the parent table Department and the child table Faculty. If you attempt to delete a row from Department, the foreign key constraint error will be thrown if there is a related row in the Faculty table. then there should not be any existence of Faculty.

Solutions to Foreign Key Constraint Error

  1. This first option is not recommended, but is included for completeness.  Delete all respective foreign key rows from the child table, only then you will be able to delete from the parent table.
  2. This solution involves modifying your table’s schema. This is the best practice solution. When you are defining table structures, define DELETE CASCADE on the foreign key table. Example:
CREATE TABLE Department –- Parent Table
 (Department_num INT PRIMARY KEY,
 Department_Name CHAR(20));
 
CREATE TABLE Faculty –- Child Table
 (Faculty_num INT,
 Faculty_Name CHAR(20),
 FOREIGN KEY (Faculty_num) REFERENCES Department_num
 ON DELETE CASCADE)

Because ON DELETE CASCADE is defined for the dependent table, when a row of the Department table is deleted, the corresponding rows of the Faculty table are also deleted.

See also  Understanding MySQL Port Configuration and Management

Suggestion: Ensure your application first deletes the row from the child table, before deleting from the parent table. Or use CASCADE DELETE when defining your schema

Helpful Hint: Examine the delete rule for all descendent/Parent tables to determine the cause of the problem. The specific tables which are involved can be determined from the relationship ‘constraint-name’. The specific descendent/Parent table row is known by RID X’rid-number’.

SQL State : 23504

Leave a Comment