Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unanswered: Deleting a foreign key relation then adding it again

    Hello,

    I'm currently updating data in an oracle database using sql developer.I have 2 tables that have a foreign key relation, I tried to update both of them together but it didn't work so I dropped the foreign key relation and updated them.Now I'm trying to add the relation again but it fails because the table has data. How can I solve this problem?
    Or if there is another way to update both tables I 'd highly appreciate it

    Thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hello,
    Quote Originally Posted by sarorelasoul View Post
    I'm currently updating data in an oracle database using sql developer.
    How exactly? Has SQL developer any special functionality for updating or did you simply issue UPDATE statement there?
    Quote Originally Posted by sarorelasoul View Post
    I have 2 tables that have a foreign key relation, I tried to update both of them together but it didn't work
    For me, it always works. If you try to update column which is present in foreign constraint, Oracle fails with foreign key violation (ORA-02291 or ORA-02292); but it still does work as Oracle does not allow to put column values breaking it.
    Quote Originally Posted by sarorelasoul View Post
    so I dropped the foreign key relation and updated them. Now I'm trying to add the relation again but it fails because the table has data.
    You want to say "data which do not comply with it", right?
    Quote Originally Posted by sarorelasoul View Post
    How can I solve this problem?
    Simply fix your data to meet the foreign key constraint.
    Quote Originally Posted by sarorelasoul View Post
    Or if there is another way to update both tables I 'd highly appreciate it
    One way is to use deferred foreign key constraint and do all updates in one transaction - check of these constraints is done on COMMIT.
    This option is described in SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

Tags for this Thread

Posting Permissions

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