|
View previous topic :: View next topic
|
| Author |
Message |
Sriram K
New User
Joined: 17 Feb 2009
Posts: 32
Location: Bangalore
|
|
|
|
I used the below query with ON DELETE CASCADE option to delete rows from all the tables ( Parent & child ).
DELETE FROM ALSR5. table1
WHERE
BOR_SSN = ********* AND
BOR_ACT_SFX = '1' AND
BOR_SEQ_NUM = 01
ON DELETE CASCADE
Here table1 is the parent table and we have got many child tables. The problem here is the table is defined with DELETE RESTRICT option because of which i couldn't able to use ON DELETE CASCADE option. So i got -199 error on spufi.
Let know if there are any other keyword with which we can achieve this. |
|
| Back to top |
|
 |
Debasis Misra
Warnings : 1
New User
Joined: 16 Sep 2008
Posts: 72
Location: Bangalore
|
|
|
|
Sriram,
i think you atr trying to post a new topic |
|
| Back to top |
|
|
ovreddy
Active User
.jpg)
Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.
|
|
|
|
Hi,
DELETE RESTRICT restricts deletion of child table rows whenever a parent row is deleted. In turn you cannot delete any thing. I hope this will not work even in reverse direction of Deleting child table then deleting parent if it is a foreign key with DELETE RESTRICT in both parent and child tables.
The only way would be to disable this for some time and delete needed rows. Then reenable it. Contact your DBA to do this.
Thanks,
Reddy |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi Sriram
Could you please post the entire error message generated by DB2. The SQLCODE -199 says it is purely a syntax error. Can you please check at your shop whether the ON DELETE CASCADE can be used with the DELETE statement. To my knowledge, ON DELETE CASCADE is a RI rule, that can be mentioned with DDL, not DML. Check your DB2 version you are using and also check whether this statement is a valid one for your DB2.
Regards
Raghu |
|
| Back to top |
|
|
ruodeer
New User
Joined: 06 Jul 2007
Posts: 58
Location: home
|
|
|
|
| Hi, I believe
is kind of syntax of referential constraint, you can not use it in Delete statement, please check DB2 SQL manual. |
|
| Back to top |
|
|
senthilnathanj
New User
Joined: 31 Jul 2007
Posts: 47
Location: chennai
|
|
|
|
hi,
SQLCODE -199
Explanation: A syntax error was detected in the statement at the point
where the keyword keyword appears.
For Example,
Create a table COLOR with the Parent table.
| Code: |
CREATE TABLE COLOR
(
c_id INTEGER NOT NULL PRIMARY KEY,
c_desc CHAR(20) );
|
Create a Child table OBJECT as mentioned below:
| Code: |
CREATE TABLE OBJECT
(
o_id CHAR(2),
o_desc CHAR(20),
c_id INTEGER REFERENCES COLOR(c_id) ON DELETE CASCADE); |
| Code: |
| DELETE FROM COLOR WHERE c_id =2 |
you can create like this and execute.
Please let me know if any concerns.. |
|
| Back to top |
|
|
|
|
