Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    16

    Unanswered: How to drop the foreign keys in a table.

    I have a table with 7 foreign keys.
    How can I drop then and again add them to a table in DB2?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    ALTER TABLE DROP FOREIGN KEY

    ALTER TABLE ADD FOREIGN KEY

    Look them up in the manual.

    Andy

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2look with drop option could help
    db2look - DB2 Statistics and DDL extraction tool command
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jul 2011
    Posts
    16
    Are there any disaadvantages of dropping the foreign keys from the table.
    Then deleting all the data from parent table and child table and then again adding the foreign keys.
    As by this way it reduces my time of deletion of records from Parent table.
    Also if there are any disadvantages in doing so, is there any other way to do the same.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is the purpose of the test / operation and what are you trying todo
    please explain clearly whit details.. we can not see what you are thinking..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2011
    Posts
    16
    I have two tables Parent table and the Child table.
    The Child table has foreign keys refering to the Parent table.
    Both the tables contain around 1 lack rows, now I want to delete all the rows in both the tables.
    It takes around 1 min for the Child table to be cleared but for Parent table it takes 30 minutes.
    So I dropped the Foreign key relation and then the Parent table is also cleared in 1 min.
    Then I again assign the foreign keys to the child table before a fresh insertion.
    So is this thing has any disadvantages, and is there any other way to perform the above steps. I also tried CASCADE ON DELETE for Child table but then also it is taking 30 minutes to delete the data from both the tables.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I would : take db2look - remove foreign keys - clear tables with truncate command or load from /dev/null - reload data - re-execute db2look.. depending on the size and nbr of indexes.. indexes can be dropped or kept during the load
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Aug 2011
    Location
    London, UK
    Posts
    3
    To drop a FOREIGN KEY constraint, use the following SQL:

    MySQL:
    ALTER TABLE Orders
    DROP FOREIGN KEY fk_PerOrders

    SQL Server / Oracle / MS Access:
    ALTER TABLE Orders
    DROP CONSTRAINT fk_PerOrders

Posting Permissions

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