HeatWare.net header image
HeatWare.net
TwitterRSSEmail
  • Home
  • Windows
    • Windows 8
    • Windows 7
    • Windows Vista
    • Windows XP
  • Linux/Unix
  • Mac
  • Mobile
    • Android
    • iOS
    • Phones (Help / Resources)
  • Software / Programming
    • Free Software
    • Programming – General
    • PHP
    • Ruby/Rails
    • Quality Assurance (QA)
    • Software – General
    • Software Help
    • Databases
  • Cool Websites
  • Other
    • Deals & Bargains
    • News
    • Video Games
    • Hardware
    • Electronics
  • About

0 comments / April 24, 2014 / guest / Databases

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

This article will explain the cause and solution to DB2 SQL Error-532, 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.

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

Related Posts

  • Create a compressed MySQL database dump/backup (Linux)
  • Solved! Installing mysql gem - extconf.rb failed error
  • Find PostgreSQL database size using SQL 'Select'
  • How to delete/drop a constraint in PostgresSQL
  • MySQL 5.6 - How to log slow queries (Linux)

DB2 mysql SQL Server

0 comments… add one
Cancel reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Why You Should Consider Diving Apps
  • How to Buy a Smartphone that Fits Your Budget
  • How to Overcome Frustrating PDF Stress
  • Convert PDF to Word: Easy, Reliable and Quality Conversion
  • PDF to Excel App ­ – A New Way of Handling Tricky Documents

Recent Comments

  • Valarie Walter on Basic Troubleshooting Steps for your Cell Phone
  • John Mists on A Brief History of Android OS
  • syarif on PostgreSQL: How to reload config settings without restarting database
  • Raghu on How to SSH to a server using Ruby – Part I
  • francisco clemente on Basic Troubleshooting Steps for your Cell Phone

Tags

ACSLS Android Cell Phones Cool Software Cool Websites Databases Facebook File System Free Software G2x Galaxy S5 Hot Deals iOS iPhone ISO LG Linux Linux/Unix Mac Mobile mysql OpenStack OS X PHP Postgres PostgreSQL Printers Programming ruby Samsung Galaxy S6 Shell Smartphones Sun T-Mobile Tips Tips & Tricks Ubuntu Unix Virtualization VMWare Windows Windows 7 Windows 8 Windows Vista Windows XP

Latest Tweet

Follow @HeatwaredotNet

SP
@HeatwaredotNet

  • Why You Should Consider Diving Apps https://t.co/Is41cdUv2I #diving-apps
    about 4 years ago

All Categories

Other Links

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2015 — HeatWare