Primary Key and Unique Index -- sql scripts generated by SQL Developer

Question

When export sql scripts by SQL Developer there are multiple options available, but either way there have to generate a UNIQUE INDEX on primary key like this

CREATE UNIQUE INDEX "SYS_C0018099" ON "TRANSACTION" ("ID") 

and add PRIMARY KEY to the same table and same column

ALTER TABLE "TRANSACTION" ADD PRIMARY KEY ("ID")

So the question is: does it looks like kind of redundancy? I thought creating a primary key on a column should by default create an unique index on that column too? So why the first command is necessary?

And this may cause data redundancy?

I am on Oracle 11g so please share any ideas about why it should look like above.

Thanks in advance.

Answer 1

There is no redundancy - or only a little bit :)

The second command will use the index available if exists. Otherwise(if first DDL does not exists) will create an index.

The split into two commands is useful when you had given a proper name to the index and want to keep it.

UPDATE: The link indicated by Thomas Haratyk is a must read, I really like it: http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/

UPDATE2: a_horse_with_no_name is right, it can be done in a single statement like:

alter table TRANSACTION
add CONSTRAINT pk_test PRIMARY KEY (id);

So, it will keep the name(won't create a sysblalbla object name) and if you use the 'USING INDEX' keyword you can specify index atributes, for example storage atributes.

But again, you will not have any problems with those two statements, only an index is created.

Probably SQL Developer prefer to get a ddl per object and there might be cases when it's better its way.