Home » SQL & PL/SQL » SQL & PL/SQL » Replacing a unique index with primary key (merged 3)
Replacing a unique index with primary key (merged 3) [message #391486] Thu, 12 March 2009 06:33 Go to next message
guyd
Messages: 1
Registered: March 2009
Junior Member
Hi,

I've just converted a whole database from another vendor to Oracle 10g. Apparently, the converted database has no primary keys and only unique indices. Is there an easy and non-harmful way of converting all unique index to primary key, assuming that there's only one unique index per table?

Thanks,
Guy.
Re: Replacing a unique index with primary key [message #391492 is a reply to message #391486] Thu, 12 March 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
alter table mytable
add constraint pk_mytable
primary key (<column list of unique index>)
/

Regards
Michel
Re: Replacing a unique index with primary key [message #391493 is a reply to message #391486] Thu, 12 March 2009 06:42 Go to previous messageGo to next message
rsampathy
Messages: 2
Registered: July 2006
Junior Member


Unique key - allowes NULL Values.
Primary Key - not allowed NULL values.




Re: Replacing a unique index with primary key [message #391495 is a reply to message #391486] Thu, 12 March 2009 06:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This example will show you how to extract the DDL for your unique constraints.
All you need to do is to loop through a list of the unique constraints, get the DDL for each one, alter the DDL with REPLACE, and then execute the new DDL. You can include an optional step of dropping the unique constraint as well.
create table test_145 (col_1 number, col_2 number, col_3 number
                      ,constraint test_145_uq1 unique (col_1,col_2)
                      ,constraint test_145_uq2 unique (col_3));
                      

begin
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
end;
/

select dbms_metadata.get_ddl('CONSTRAINT',constraint_name) 
from   user_constraints
where  constraint_type = 'U'
and    table_name = 'TEST_145';

begin
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
end;
/
Previous Topic: SQL Database Name. (HEEELP)
Next Topic: Top records based on a column
Goto Forum:
  


Current Time: Fri Dec 02 12:42:41 CST 2016

Total time taken to generate the page: 0.13435 seconds