Forum Stats

  • 3,741,750 Users
  • 2,248,473 Discussions
  • 7,861,976 Comments

Discussions

Bug altering table? Deleting column with PK prevents new PK

38528
38528 Member Posts: 243
I created a table of four fields through the HTML db front end management tool. I made one of the columns a primary key and it created the proper constraint. Then, I deleted the column. Now I can no longer make a primary key on the table because it says there is one. There is no way to see the existing primary key and remove it.

Comments

  • 241066
    241066 Member Posts: 776
    When you say you deleted a column, did you mean drop it ? It works fine for me via the GUI, here's the steps I took (and the relevant SQL that the GUI executed)

    Logged in as HR
    From the Object Browser go to Create Table
    Create a 4 column table - first column a number, other columns characters
    Select Not Generated for Primary Key and choose the first column as the Primary Key
    No Foreign Keys created
    No (additional) Constraints created
    Confirm Table Creation - here's the SQL generated
    CREATE table "TEST" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(4000),
    "COL3" VARCHAR2(4000),
    "COL4" VARCHAR2(4000),
    constraint "TEST_PK" primary key ("COL1")
    )
    /

    Table created.
    In the object browser, looking a the Test table, click the Drop Column button
    Select Col1 as the column to be removed
    Confirm the Drop Column action - here's the SQL generated
    alter table "TEST" drop column
    "COL1"
    /
    Column gets dropped
    In the object browser, looking a the Test table, click the Constraints tab
    Table has no constraints, click Create
    Select a constraint type of Primary Key
    Select COL2 as the Primary Key
    Confirm the Create Constraint action - here's the SQL generated
    alter table "TEST" add constraint
    "TEST_CON" primary key ("COL2")
    /
    All works tickety-boo

    Did you use different steps ?
  • 38528
    38528 Member Posts: 243
    Thanks for looking into it. You did the steps correctly. Except I now realize what my problem is. When I created the table, my Col2 was set to be unique. After dropping col1, I tried to make col2 the primary key which doesn't work with a unique constraint already there. I should have read the error message better. I was hung up on the error text that said, "... primary key already exists in the table".

    Looks like my mistake. Thanks for helping me find it.
This discussion has been closed.