Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Steps for update Primary key for existing table

Steps for update or alter Primary key for existing table

Suppose we want to add/drop more column in my primary key of existing table. Basically we want to modify the primary key of the existing table.

Following are the steps used for alter and modify the Primary key of existing table:

1. Check all the Constraint with index name created on a table.

COL CONSTRAINT_NAME FOR A20
COL OWNER FOR A6
COL TABLE_NAME FOR A20
COL INDEX_OWNER FOR A6
COL INDEX_NAME FOR A20
SELECT CONSTRAINT_NAME,OWNER,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME FROM DBA_CONSTRAINTS
WHERE TABLE_NAME='TEST11' AND INDEX_NAME IS NOT NULL;

CONSTRAINT_NAME   OWNER  C TABLE_NAME  INDEX_ INDEX_NAME
----------------- ------ - ----------- ------ ------------
TEST_CONS         SYS    P TEST11      SYS    TEST_CONS

2. Check the column detail of primary index and other index.
Suppose you want to create a primary index which column is present as it is in another index of table. Then you need to drop that index also.

SET LINE 250 PAGES 1000
COLUMN B.TABLESPACE_NAME FORMAT A10
COLUMN TABLE_NAME FORMAT A10
COLUMN INDEX_NAME FORMAT A20
COLUMN COLUMN_NAME FORMAT A16
COLUMN TABLESPACE_NAME FORMAT A8
COLUMN LAST_ANALYZED FORMAT A9
COLUMN DEGREE FORMAT A1
SELECT A.TABLE_NAME, A.INDEX_NAME, A.COLUMN_NAME,B.TABLESPACE_NAME,B.UNIQUENESS
FROM DBA_IND_COLUMNS A,DBA_INDEXES B WHERE B.INDEX_NAME = A.INDEX_NAME AND A.TABLE_NAME = 'TEST11'
ORDER BY A.TABLE_NAME, A.INDEX_NAME,A.COLUMN_POSITION,A.COLUMN_NAME;

TABLE_NAME INDEX_NAME     COLUMN_NAME  TABLESPA DESC UNIQUENES
---------- -------------- ------------ -------- ---- ---------
TEST11     TEST_CONS      ID           SYSTEM   ASC  UNIQUE

3. Drop the Primary key which you found in first query.

ALTER TABLE table_name DROP CONSTRAINT constraint_name ;

Example:
ALTER TABLE TEST11 DROP CONSTRAINT TEST_CONS;

4. If any index is already present with same column then you need to drop that column also.

DROP INDEX index_name;

5. Create the Primary key

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col_name,col_name);

Example:
ALTER TABLE test11 ADD CONSTRAINT test_con PRIMARY KEY (ID,BATCH);

Advertisements