CODEWITHSUNDEEP
oracle-databaseindexingconstraints
vicsz
vicsz

Reputation: 9754

Possible to create validating constraints / primary keys without indexes?

I'm working a data migration project, where some tables will only be loaded once. I.e. a table will be fully loaded using only one Select From Insert Into.

I want to assure data integrity with the usage of primary keys and constraints.

With Oracle, when ever I create a primary key or constraint Oracle will automatically create the corresponding index for me.

Is it possible to disable the creation of the index? Note that I still want the constraint to be validated as needed ... so that in the off chance that Oracle actually needs to do a validation it will either create the index (on an as needed basis) .. or do a Full Table Scan.

The reason that I want to disable or defer this index creation, is that I noticed that most of the indexes (for constraints) that get created don't actually get used. I do this by turning on Usage Monitoring for all indexes, running the data migration process, and then verifying v$object_usage.

Upvotes: 1

Views: 2189

Answers (2)

DCookie
DCookie

Reputation: 43533

Oracle uses a unique index to enforce a primary key constraint. You can't create a primary key without the corresponding index being created unless you create it in the disabled state, in which case you don't get your primary key enforcement unless and until you enable it.

EDIT:

If there is an index that Oracle can use already on the column(s) used in a primary key, then Oracle will use that index to enforce the constraint, unique or not. If Oracle must create the index, it will be unique, unless the constraint is deferrable in which case it will be nonunique. Thanks to Shannon and Adam for comments.

Upvotes: 5

Ben
Ben

Reputation: 52893

EDIT The bit answering the question in this answer is wrong.

DISCLAIMER: I don't believe this is wise

It's possible to replicate the actions of a primary key constraint without actually creating a primary key. This is because the constraint, non-index, part of a PK constraint is effectively a combination of a not null constraint and a unique constraint, which are stackable. You then don't have an index. So, yes. It's possible.

But:

  1. The idea of a table where the PK is never used seems unlikely.
  2. Having just had to solve the problems created when someone decided that being overly fancy with PKs was a good idea about 10 years ago.

I would highly recommend you go down the normal route and create a primary key. Space is cheap and you'll probably regret it later if you don't.

Upvotes: 0

Related Questions