13 Replies Latest reply: Apr 13, 2008 8:37 AM by 626082 RSS

    Drop Primary Key

    626082
      Hi I need a help for removing a PK from a table which has multiple primary keys. I think the code at the below just removes the constraint. I don't want remove the constraint, as I said there multiple primary keys and I want to remove just one of them to make it foreign key. Could you help me please? Thanks
      ALTER TABLE tablename
      drop CONSTRAINT pk_columnname;
        • 1. Re: Drop Primary Key
          Anurag Tibrewal
          Hi,

          Multiple primary key? I suppose there can be only one primary key.

          By primary key if you meant composite key(group of columns together acting as a primary key) and you want to remove one column from it, then you can use

          alter table modify constraint command

          Regards
          • 2. Re: Drop Primary Key
            damorgan
            It is impossible, by definition, for a table to have more than one primary key. So possibly you are talking about foreign keys referencing the primary key or something else entirely.

            Post your full version number and an accurate description of what it is you are trying to do. Do it in business terms ... not code.

            My suspicion is that what you want is:
            alter table <table_name>
            drop primary key cascade;
            • 3. Re: Drop Primary Key
              626082
              Thank you for the replies.
              CREATE TABLE tablename
              (
              col1        INTEGER,
              col2      INTEGER,
              col3      NUMBER(1),
                   CONSTRAINT pk_tablename PRIMARY KEY (col1,col2)
              );
              So If I want to drop the col2 from primary key, can I do that with out dropping the constraint then defining the pk and fk. Or do I have to drop the constraint and alter pk and fk?
              • 4. Re: Drop Primary Key
                Anurag Tibrewal
                Hi

                alter table tablename modify primary key(col1);

                Regards
                • 5. Re: Drop Primary Key
                  damorgan
                  Drop the primary key constraint.

                  Create a new primary key.
                  • 6. Re: Drop Primary Key
                    626082
                    I tried to do it and I cannot create a primary key. These are my code's structure that I used
                    PROMPT Drop Primary Key
                    ALTER TABLE TableName 
                    DROP CONSTRAINT pk_ConstraintName;
                    
                    PROMPT Create a primary key
                    ALTER TABLE TableName
                    ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (ColumnnnName);
                    -------------------------------------------------------------------------------------------------------------
                    As output I get
                    Drop Primary Key
                    
                    Table altered.
                    
                    Create a primary key
                    ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (ColumnnnName);
                                                 *
                    ERROR at line 2:
                    ORA-02437: cannot validate (Owner.PK_ConstraintName) - primary key violated
                    So what is the problem and how can I solve it, and please be specific as much as possible thanks.
                    • 7. Re: Drop Primary Key
                      SomeoneElse
                      In order to be a primary key, the column must be not null and unique for every row in the table. Apparently it isn't.
                      • 8. Re: Drop Primary Key
                        626082
                        There are duplicated data but it was primary key before. I dropped the constraint and tried to make the column as primary key again but I can't do it. So is there any other solution for this case? Thanks
                        • 9. Re: Drop Primary Key
                          631985
                          Previously your primary key consists of col1 and col2. So the combination of col1 & col2 was unique for each row. since you removed one of the column from the primary key constraint now its showing error. Because the other column has duplicate data.

                          you would need to remove duplicate rows to add this constraint.
                          • 10. Re: Drop Primary Key
                            626082
                            OR how can I just make the col1 as a primary key and col2 as a foreign key on the created table. I can not create the table from beginning. That is what I am trying to do.
                            CREATE TABLE tablename
                            (
                            col1       INTEGER,
                            col2       INTEGER,
                            col3       NUMBER(1),
                                  CONSTRAINT pk_tablename PRIMARY KEY (col1,col2)
                            );
                            • 11. Re: Drop Primary Key
                              626082
                              ok I guess I got it now. So I shouldn't remove any of the primary key consists col1 or col2, or if I do have to remove the duplicate data or the null data, right?
                              • 12. Re: Drop Primary Key
                                631985
                                Yes, you are right.
                                • 13. Re: Drop Primary Key
                                  626082
                                  Thank you for the explanation, I appreciate it.