Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Unable To Modify Table

    I seem to have an issue where I can't modify a table due to another tables foreign key association:

    Code:
    trinity=# \d developers
             Table "public.developers"
        Column    |      Type      | Modifiers
    --------------+----------------+-----------
     id      | character(10)  | not null
     name    | character(50)  | not null
     address | character(50)  |
     city    | character(50)  |
     state   | character(2)   |
     zip     | character(10)  |
     country | character(50)  |
     phone   | character(50)  |
     email   | character(255) | 
    Indexes:
        "developers_pkey" PRIMARY KEY, btree (id)
    Referenced by:
        TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) REFERENCES developers(id)
    Now I want to change the formatting of field data in 'id' in table 'developers':

    Code:
    trinity=# SELECT id FROM developers;
         id
    ------------
     1000000001
     1000000002
     1000000003
     1000000004
     1000000005
     1000000006
    (109 rows)
    Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table.

    Code:
    trinity=# UPDATE developers SET id = '1000' WHERE id = '1000000001';
    ERROR:  update or delete on table "developers" violates foreign key constraint "fk_orders_developers" on table "orders"
    DETAIL:  Key (id)=(1000000001) is still referenced from table "orders".
    How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The idea is to avoid 'orphan' records, by limiting the ability to change records where dependent records exist. IOW, you maintain the integrity of the data.

    If you have a situation where you sometimes need to alter data in the way you mention, try altering/replacing the existing constraint to take advantage of pg's UPDATE CASCADE feature of the foreign key constraint.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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