Renaming PK constraints, indexing existing tables

Here's my challenge - hopefully someone can point me in the correct direction.

We have built an application for a client that uses Oracle tables. The tool we use for development handles creating tables and views. However, constraints get 'Oracle generated' names - such as SYS_C001769560. The DBAs at the install site are saying no way! They want these constraints to have real names and to be indexed. Ok, can't say that I blame them much...

However, I'm a bit stumped as to how to most logically handle this. I can't create the index with the constraint on. So, I drop the constraint first. Simple enough. But then do I create the index with 'UNIQUE' or do I leave that alone knowing that I'm going to create a constraint?

Secondly, what exactly is the syntax to modify the table? I'm puzzling through the alter table modify (modify_colum_options) tree but quite frankly am confused. It would appear that I can create the index on the fly if I puzzle out the column_constraint piece(?). Oh, and needless to say, if I got that working I must be sure to place the index in the appropriate tablespace.

Any suggestions or ideas here? Embarassingly enough, I was an Oracle DBA back in the Oracle 6 and early 7 days, but I've forgotten entirely too much. *sigh*

Any thoughts or sql snippets would be greatly appreciated.

Thanks in advance,

Handy (or not so handy as the case may appear to be...)

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 11 2001 - 11:16:52 CST