Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: delete without removing constraints

    Hi,
    I've two tables tableA and tableB.
    both have two columns schedule_id, season_id.

    Table A has the dependant row in table B using Schedule_id.
    there is a contsraint tableB_const01 on schedule_id with properties - ON DELETE NO ACTION.

    I want to delete rows from table A where season_id<2006. when i try to apply the delete command on table A, delete fails. Then I tried to delete rows from table B and tried to delete table A again. This time also db2 delete fails.

    How can I delete the rows in this situation? actually my requirement is to delete all the table's data whose season_id<2006 with out removing the check constraints.

    what is the best way to delete the rows from all the tables?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Normally you delete all the appropriate rows from the child table first and then all the rows from the parent table. In order to give you a more specific answer, please provide the DB2 version, OS, the DDL for the two tables and the exact error message you are getting.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    many thanks for the solution.
    db2 - v8, os- aix, in the DDL no delete option is mentioned. I think the default is on delete no action.

    The problem is, even it has foreign key constraint, the other feilds are not matching in both tables. I mean the season_id (reference key to delete the data from the table) is different for 41 records in both tables. I think, that's the reason db2 is not allowing to delete.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why do you think that other columns have anything to do with a foreign key and the columns in a foreign key?

    If you want to delete something from tableA, but there are still rows in tableB that reference the deleted rows from tableA, you would violate the referential constraint (aka foreign key). Since the referential action on the foreign is is NO ACTION, you must either ensure that the foreign key constraint is not violated at the end of the DELETE statement (you could use triggers for that), or you have to delete the dependent rows in tableB first.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by ani_dbforum
    many thanks for the solution.
    db2 - v8, os- aix, in the DDL no delete option is mentioned. I think the default is on delete no action.

    The problem is, even it has foreign key constraint, the other feilds are not matching in both tables. I mean the season_id (reference key to delete the data from the table) is different for 41 records in both tables. I think, that's the reason db2 is not allowing to delete.
    I also asked for the ddl and the error message. There are several different reasons why it is not working and having this info will help us to help you fix the problem.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •