Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: DB2v8.2 LUW. Unable to drop Foreign Key constraints...although DROP constraint...

    DB2v8.2 LUW. Unable to drop Foreign Key constraints...although DROP constraints returns undefined name. Am I missing somrthing here?


    Hi all,
    I have a perplexing DB2 Drop command situation. Environment I have tried severally to drop Foreign Key constraints on a set of

    tables and each time I get a SQL0204N "SQL071030104307060" is an undefined name. ".
    However, when I go back to look at the table or load data, I get a constraint error telling me that the same constraint that is a

    undefined name exist. I run a generate ddl command for the table, indeed the constraint still do exist!

    Be assured that I am logged in to the right instance, database, etc.
    What can possibly be the problem here? How can I make sure these constraints are dropped?
    Here's an example of one that I have tried to drop but no avail:
    ALTER TABLE "ABCDE "."F_94_RPT_ADDR_T"
    ADD CONSTRAINT "SQL071030104309760" FOREIGN KEY
    ("FAHIS_NBR_R")
    REFERENCES "REPSP "."F_191_ABCDE_RPT_T"
    ("FAHIS_NBR_R")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION


    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by pagwu
    DB2v8.2 LUW. Unable to drop Foreign Key constraints...although DROP constraints returns undefined name. Am I missing somrthing here?


    Hi all,
    I have a perplexing DB2 Drop command situation. Environment I have tried severally to drop Foreign Key constraints on a set of

    tables and each time I get a SQL0204N "SQL071030104307060" is an undefined name. ".
    However, when I go back to look at the table or load data, I get a constraint error telling me that the same constraint that is a

    undefined name exist. I run a generate ddl command for the table, indeed the constraint still do exist!

    Be assured that I am logged in to the right instance, database, etc.
    What can possibly be the problem here? How can I make sure these constraints are dropped?
    Here's an example of one that I have tried to drop but no avail:
    ALTER TABLE "ABCDE "."F_94_RPT_ADDR_T"
    ADD CONSTRAINT "SQL071030104309760" FOREIGN KEY
    ("FAHIS_NBR_R")
    REFERENCES "REPSP "."F_191_ABCDE_RPT_T"
    ("FAHIS_NBR_R")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION


    Thanks
    The name in the error and the name in the DDL are different. Have you tried using the Control Center to drop the constraint?

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I concur with Andy: check the DB2 catalog views that you use the correct name of the constraint.

    p.s: That's the reason why you should use an explicit name for all constraints and not rely on system-generated names.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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