In which order should we apply primary key, foreign key constraints and create index if the Oracle table has data?

Note: I'll use bullets so that it is easier to read, as it is easy to get lost in long sentences.

My thoughts on the subject; see if anything of this helps.

Well,

• as you can't create a foreign key constraint if column(s) it references aren't part of primary or unique key
• you'll obviously first have to create primary key constraints
• and then foreign key constraints

When you

• create a primary key constraint,
• Oracle automatically creates index that supports it, unless there's already an index you can use (with the USING INDEX clause)
• which means that you can "skip" some indexes (those for primary key constraints as they'll already exist) and virtually save some time
• and create "other" indexes

On the other hand,

• if you first create unique index on future primary key columns and
• later add primary key constraint with the USING INDEX clause, Oracle will "skip" check for possible duplicate values because unique index won't allow them

The same goes for

• NOT NULL constraint on future primary key columns; primary key doesn't allow NULLs so - if a column already is NOT NULL, enforcing primary key constraint can skip NULL check as well

I don't know

• which columns you'll additionally index, but - as you're on Oracle 11g -
• don't forget to index all foreign key constraint columns
• because you might encounter unexpected table locks if you
  • update primary key column in parent table, or
  • delete parent record

Can you do it with no logging and in parallel? Yes:

SQL> create table test (id number, name varchar2(20));

Table created.

SQL> create unique index ui1_test_id on test (id) nologging parallel 20;

Index created.

SQL> alter table test add constraint pk_test primary key (id) using index ui1_test_id nologging parallel 20;

Table altered.

SQL>