This content has been marked as final. Show 5 replies
in the Physical model you can specify for this PK the clause USING INDEX in the properties of the PK, you should select BY INDEX NAME value,
then the tab of Using Index will be enabled to specify the tablespace,sorting ,logging ,etc and If you want to specify a existing index,
then the DDL generated wiill look like this ;
ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_PK PRIMARY KEY(...) USING INDEX TABLESPACE TB_INDX01;
With this statement the Oracle will create an index with the same name than the PK,
take into account that if you have another unique index qith the same columns Oracle will use the old one saving the necessity to create a new one,
Maybe I'm not so clear, if you have more questions or I couldn't explain me well , please let me know
Thanks for your suggestions, it helped me find some new areas to try out.
For selecting an existing index, I can only choose one that I had defined earlier (no surprise) but I was not allowed to save an index with the correct name.
I think I have accomplished it by:
- Definiing unique indexes with the names I want (first)
- Defining pk and unique constraints, and let the tool change the names (suffixing with v1, for example)
- In the physical model, open the PK constraint
- Rename the PK constraint there (remove the v1 suffix)
- Choose By Index Name for Using Index, then use the Using Index tab to choose the corresponding index
- Repeat for Unique constraints
Now the DDL generates the way I want. In the relational model, the PK and unique constraint names still have the annoying v1 suffixes.
If you want to have a PK and the index of the PK with the same name,
with this sintaxys will be acomplish this:
ALTER TABLE table_name ADD(CONSTRAINT pk_name PRIMARY KEY ( ) USING INDEX);
if you create the PK in the relational model, not create any index,
in the physical model you select BY INDEX NAME in the USING INDEX option of the primary key,
the resulted DDL will acomplish your necessities,
Oracle will create an unique index with the name of the PK and assign to the primary key.
Rename the PK constraint there (remove the v1 suffix)I'm glad you have your solution. Indeed objects in physical model can have name that differ from one in relational. We have bug logged for this PK/index name problem and at some point it'll be possible to have them with same name in relational model.