Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    Unanswered: Dropping columns in a table that has foreign keys

    I have a table A, and tables B,C,D and E has foreign keys referencing A.
    What's the best way to drop 2 columns in table A?

    All of the tables have data in them.

    The only way I know of doing it is to:
    - copy the data in tables B-E to a text file then drop the tables
    - create a temp table with table A excluding the columns I don't want
    - re create tables B - E and load them

    My way involves dropping tables B - E, and I want to know if there's a betta way.

  2. #2
    Join Date
    Apr 2002
    Location
    Calgary, Canada
    Posts
    30
    why dont you drop the constraints on tables B C D E ?

    if they are referencing A then there will be no need for them anymore...right ?

    alter table B drop constraint <constraint_name>;

    and so on....

    then u would have to recreate your A table with the correct data of course

    Last edited by MGZ; 11-18-02 at 23:12.

  3. #3
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    drop column

    in the new version 7.3 is the feature
    alter table drop column implemented

    RC1 is out and RC2 follows
    In about 1-2 weeks the final release of 7.3 is out
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  4. #4
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76
    Originally posted by MGZ
    why dont you drop the constraints on tables B C D E ?

    if they are referencing A then there will be no need for them anymore...right ?

    alter table B drop constraint <constraint_name>;

    and so on....

    then u would have to recreate your A table with the correct data of course
    I'm using postgresql 7.2.2 which doesn't allow the users to drop foreign key constraints. I'll either have to wait for postgresql 7.3 or just do it my way.

  5. #5
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    right

    You are absolutely right
    I always change the structure this way
    dump->edit->dump in database
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

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