473,883 Members | 1,767 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How I can delete rows in one table with foreign keys

Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTIO N.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdat e()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTI D AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045 710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.
Greetings

Iván

Sep 7 '06 #1
6 9777
Hi, Ivan:

OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?

--Jeff

Ivan wrote:
Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTIO N.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdat e()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTI D AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045 710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.
Greetings

Iván
Sep 7 '06 #2
Excuse me, to that you talk about with autocommit setting ?
jefftyzzer wrote:
Hi, Ivan:

OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?

--Jeff

Ivan wrote:
Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTIO N.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdat e()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTI D AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045 710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.


Greetings

Iván
Sep 7 '06 #3
Right. My reasoning is that under the explanation for the error you're
getting, it says (in part):

"The application program attempted to access an object within the
same unit of work in which either:
<SNIP>
The application program issued a statement against the object
that added or dropped a constraint."
<SNIP>

And under the user response section, it says (in part):

"Modify the application program so there is no attempt to access
an object within the same unit of work in which the modification
was made."

So, the issue surrounds units of work. If you've got autocommit turned
off (in which case you need to issue explicit COMMITs after DDL and DML
statements), then it sounds like you need to add a COMMIT after you do
the ALTER.

--Jeff

Ivan wrote:
Excuse me, to that you talk about with autocommit setting ?
jefftyzzer wrote:
Hi, Ivan:

OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?

--Jeff

Ivan wrote:
Hello to all and thanks for answer to my topics.
>
>
I made one stored procedure that delete one table, but when
call/execute the procedure this show
>
SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTIO N.FK_SCLI " restricts the deletion.
>
Then in the procedure it adds one delete of the foreign keys. This it
is the procedure
>
CREATE procedure eis.DeleteUpdat e()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);
>
SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;
>
>
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTI D AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO
>
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
>
DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
>
>
END FOR;
>
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045 710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
>
>
But when call this stored procedure show the following message
>
SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.
>
What I really need with the procedure is that it allows me to delete
registries or rows of the table.


Greetings

Iván
Sep 7 '06 #4
Ivan wrote:
Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTIO N.FK_SCLI " restricts the deletion.
Any particular reason why you are not using ON DELETE CASCADE for the
foreign key definition?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 8 '06 #5
I put commit as follow

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;
COMMIT;

But show this message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20112N A SAVEPOINT cannot be set because a SAVEPOINT already exists
and
nested SAVEPOINTS are not supported. LINE NUMBER=9. SQLSTATE=3B002

If I use SAVEPOINTS does not leave me either

Greetings

Iván

jefftyzzer wrote:
Right. My reasoning is that under the explanation for the error you're
getting, it says (in part):

"The application program attempted to access an object within the
same unit of work in which either:
<SNIP>
The application program issued a statement against the object
that added or dropped a constraint."
<SNIP>

And under the user response section, it says (in part):

"Modify the application program so there is no attempt to access
an object within the same unit of work in which the modification
was made."

So, the issue surrounds units of work. If you've got autocommit turned
off (in which case you need to issue explicit COMMITs after DDL and DML
statements), then it sounds like you need to add a COMMIT after you do
the ALTER.

--Jeff

Ivan wrote:
Excuse me, to that you talk about with autocommit setting ?
jefftyzzer wrote:
Hi, Ivan:
>
OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?
>
--Jeff
>
Ivan wrote:
Hello to all and thanks for answer to my topics.


I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTIO N.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdat e()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;


FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTI D AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;


END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045 710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end


But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.


Greetings

Iván
Sep 8 '06 #6
Ivan wrote:
I put commit as follow

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045 710';
EXECUTE IMMEDIATE txtdrop;
COMMIT;

But show this message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20112N A SAVEPOINT cannot be set because a SAVEPOINT already exists
and
nested SAVEPOINTS are not supported. LINE NUMBER=9. SQLSTATE=3B002

If I use SAVEPOINTS does not leave me either
That's because you stated BEGIN ATOMIC.You can't COMMIT inside of an
ATOMIC BLOCK. Conflicting request.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 9 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3139
by: Carlos Ibarra | last post by:
In various places I have seen mentioned the importance of indexing foreign keys to avoid table locks on the child table on parent update/delete and full table scans when the constraint has action ON DELETE CASCADE. For example, see http://asktom.oracle.com/pls/ask/f?p=4950:8:10827638093976934265::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754, However, if I have no index on the FKs, the only mention of full table scans on the...
0
1865
by: Rainer Collet | last post by:
Hi! I have a question how to solve the folloing task: I have 2 tables referencing each over: An user table and an address table. Each user has one or no address. If an user is dropped the address should be dropped, too. If an address is dropped its reference in the user table should be set to null. I wrote the following create statements:
1
3011
by: Vinodh Kumar P | last post by:
I understand the number of foreign keys allowed is restricted by the DBMS I use. In a general relational schema design perspective how many foreign keys a table shall have? If I have large number of foreign keys what anamolies it will lead to? Is this crucial to identify all the foriegn key relationships for a table? Vinodh
11
9521
by: suma | last post by:
Hello, I want to delete duplicate rows in a table when no primary key is defined. For eg: If we have table1 with data as below, Suma 23 100 Suma 23 100 I want to delete a row from this table and retain only one row.
0
1762
by: unreal4u | last post by:
hi everybody .. first post ... hope someone answers it ... Well .. this is my problem ... I got 2 tables: /*==============================================================*/ /* Table: TIPO_EMPLEADO */ /*==============================================================*/ CREATE TABLE tipo_empleado (
1
1351
by: lundeman | last post by:
Hi list As I'm new to this list please kick me in the right direction if this question is better asked somewhere else. I'm seing some wird behavoure regarding foreign keys. My situation is this: I have 3 tables:
10
17880
by: D. Dante Lorenso | last post by:
I'd like to run a clean up command on my tables to eliminate rows that I'm no longer using in the database. I want to do something like this: DELETE FROM tablename WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE; Does anyone know how something like this could be done in PostgreSQL? I know I can search all the tables that
1
2100
by: Thomas T. Thai | last post by:
I'm looking for a better way to make use of foreign keys. Here is a sample setup: -- TESTING Foreign Keys create table mod ( mod_id int not null primary key, name varchar(32) not null default '' );
6
5919
by: bobdurie | last post by:
If i create a simple table with a foreign key constraint, does it create an implicit index on that given ID? I've been told this is done in some databases, but i need to know for sure if SQL Server does it. Has anyone heard of this before, on any other databses perhaps? Heres an example of how the foreign key constraint is being added: ALTER TABLE . WITH CHECK ADD CONSTRAINT FOREIGN KEY() REFERENCES . ()
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11153
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7975
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6002
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4225
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.