Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: how can i update a foreign key ??

    argh stuck again :

    person: member:
    email1 email1 Nr1
    email2 email2 Nr2
    email3 email3 Nr3
    email4 email4 Nr4

    email in person is a Primary Key
    email in member is a Foreign Key which references email in person

    now i want to change email1 in person to emailX and in member too.
    i tried this:

    UPDATE person,member
    SET email = 'emailX'
    WHERE email = 'email1'; <- doesn't work

    UPDATE person
    SET email = 'emailX'
    WHERE email = 'email1';
    UPDATE member
    SET email = 'emailX'
    WHERE email = 'email1'; <- doesn't work either

    any idea ?? thx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how can i update a foreign key ??

    If you have a primary key that you are going to want to update, then you have chosen a poor primary key. Email address is definitely a poor choice for a PK, as people often change their email address.

    What you might hope for is an "ON UPDATE CASCADE" option, but there isn't one.

    However, it can be done by using a DEFERRABLE foreign key:

    Code:
    SQL> create table p(pid number primary key);
    
    Table created.
    
    SQL> create table c(cid number primary key, pid number);
    
    Table created.
    
    SQL> alter table c add constraint c_p_fk foreign key(pid) references p
      2  deferrable initially deferred;
    
    Table altered.
    
    SQL> insert into p values (1);
    
    1 row created.
    
    SQL> insert into c values (10,1);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> update p set pid=2;
    
    1 row updated.
    
    SQL> update c set pid=2;
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    The foreign key (because it was defined as DEFERRABLE) is not actually checked until you try to COMMIT, at which point you have updated both tables to match so it succeeds.

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    UPDATE person,member
    SET email = 'emailX'
    WHERE email = 'email1'; <- doesn't work
    That makes sence. You cannot update two tables in the same UPDATE statement.

    UPDATE person
    SET email = 'emailX'
    WHERE email = 'email1';
    UPDATE member
    SET email = 'emailX'
    WHERE email = 'email1'; <- doesn't work either
    but what is wrong with this one? I can give you tons of example, where such DML's will work:

    SQL> create table person (email varchar2(30));

    Table created.

    SQL> insert into person values ('test@test.com');

    1 row created.

    SQL> insert into person values ('test@test.com');

    1 row created.

    SQL> insert into person values ('test@test.com');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> update person set email = 'test303@newtest.com' where email = 'test@test.com';

    3 rows updated.

    SQL> select * from person;

    EMAIL
    ------------------------------
    test303@newtest.com
    test303@newtest.com
    test303@newtest.com


    And so on, so on. Am I missing something?

    Hope that helps,

    clio_usa - OCP - DBA

    dbaclick.com

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    but what is wrong with this one? I can give you tons of example, where such DML's will work:
    I guess there is a constraint in place.
    Updating the child record fails because there is no parent record, and updating the parent won't work because there are child records.

Posting Permissions

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