5 Replies Latest reply: Jan 23, 2009 7:02 AM by Solomon Yakobson RSS

    Altering a table to add primary key.

    Sudhir
      Hi,

      I am altering a table to add a primary key with using index option.

      for ex: ALTER TABLE <table_name> ADD CONSTRAINT PRIMARY KEY (<column_name>)
      USING INDEX <do we need to give table space name here or index_name>;

      if index_name then firstly do we need to create a index on the column on which we need to create a primary key
      and then give the index name in the alter table Script.

      Please advice.
        • 1. Re: Altering a table to add primary key.
          _Karthick_
          You dont have to specify that. If an index exist on the column then oracle automatically takes that. Else oracle creates one.

          Check this out
          SQL> create table t(no integer)
            2  /
          
          Table created.
          
          SQL> create index t_idx on t(no)
            2  /
          
          Index created.
          
          SQL> alter table t add constraint t_pk primary key(no)
            2  /
          
          Table altered.
          
          SQL> select constraint_name, index_name from all_constraints where table_name = 'T'
            2  /
          
          CONSTRAINT_NAME                INDEX_NAME
          ------------------------------ ------------------------------
          T_PK                           T_IDX
          
          SQL> alter table t drop constraint t_pk
            2  /
          
          Table altered.
          
          SQL> drop index t_idx
            2  /
          
          Index dropped.
          
          SQL> alter table t add constraint t_pk primary key(no)
            2  /
          
          Table altered.
          
          SQL> select constraint_name, index_name from all_constraints where table_name = 'T'
            2  /
          
          CONSTRAINT_NAME                INDEX_NAME
          ------------------------------ ------------------------------
          T_PK                           T_PK
          
          SQL>
          • 2. Re: Altering a table to add primary key.
            Solomon Yakobson
            If you want index to be created not in user's default tablespace you have to specify tablespace name. In you want to use a specific already existing index you have to specify index name.

            SY.
            • 3. Re: Altering a table to add primary key.
              Sudhir
              Actually i need to create index on different tablespace,
              so now i need to create the index and use the insex name.

              Thanks for the advice.

              Regards
              Sudhir Reddy
              • 4. Re: Altering a table to add primary key.
                Solomon Yakobson
                Karthick_Arp wrote:
                You dont have to specify that. If an index exist on the column then oracle automatically takes that. Else oracle creates one.
                In real life one pretty much always specifies tablespace name since in most installations data and indexes are kept in separate tablespaces. Also, you need to specify index name if there is more than one index fitting PK columns and you want a specific index to be used to enforce PK.

                SY.
                • 5. Re: Altering a table to add primary key.
                  Solomon Yakobson
                  Sudhir Reddy wrote:
                  Actually i need to create index on different tablespace,
                  so now i need to create the index and use the insex name.
                  No, you do not need to precreate the index. Just specify tablespace name. Oracle will create unique index in that tablespace and will name index using same name you gave to PK. E.g.:
                  ALTER TABLE TBL ADD CONSTRAINT TBL_PK PRIMARY KEY(ID) USING INDEX TABLESPACE INDEX_TBLSPC
                  /
                  SY.