Primary key column constraints

[Martin Drautzburg]

When I create a table with a NOT NULL column constraint, DDL::Oracle correctly generates a script with a column constraint.

But when I declare a column as primary key, DDL::Oracle
generates a NOT NULL column constraint, a CREATE INDEX and an ALTER TABLE ADD CONSTRAINT sys_...

Now the name of the constraint and the name of the index did not appear in the original statement and may well clash with another constraint when the generated statement is applied to another database (even when the original CREATE TABLE *can* still be applied). This puts a limit to the idea of "reverse engeneering".

I am not sure what would be a better behavior, because OTOH I like the explicit CREATE INDEX. Maybe a USING INDEX clause would be the best solution.

[Richard Sutherland]

Yes, if you take the generated DDL for objects and create them on another database (or even in another schema within the same database), you run the risk of failing because of object name conflicts.  I don't see this as different for the SYS_xxx name or any other name.

But if reapplied to the same database/schema, this method puts everything back the way it was.

Part of the reason to do it this way is to make defrag.pl more efficient.  Ignoring the complexity of partitions, IOT's and referential integrity, a defrag basically follows these steps:

   export the tables
   drop the tables
   coalesce the tablespace
   recreate the [resized] tables, sans indexes
   import the data
   recreate the indexes

So, if one included Primary Key definitions in the CREATE TABLE statement, the import would be considerably slower.

What do you think is the ratio of unnamed Primary Keys to those where the Constraint or Index was named?  I think this should be left the way it is.

Richard