CODEWITHSUNDEEP
sqloracle-databasecreate-tableunique-constraint
Chad
Chad

Reputation: 24679

Oracle CONSTRAINT vs, CONSTRAINT *Using Index*

If a table has a constraint but no index, can I assume that the only difference is that I am leaving it to Oracle to determine how determine whether changes to the table violate the constraint, perhaps resulting in Oracle having to do table scans to determine it?

CREATE TABLE SchemaName."Table1" (
  field_one VARCHAR2(10 BYTE) NOT NULL,
  field_two VARCHAR2(30 BYTE) NOT NULL,
  field_three DATE NOT NULL,
  (etc)
  CONSTRAINT client_iu1 UNIQUE (field_one,field_two,field_three)
);

vs.

CREATE TABLE SchemaName."Table1" (
  field_one VARCHAR2(10 BYTE) NOT NULL,
  field_two VARCHAR2(30 BYTE) NOT NULL,
  field_three DATE NOT NULL,
  (etc)
   CONSTRAINT client_ak1 UNIQUE (field_one,field_two,field_three) USING INDEX fds_base.client_iu1
);

Upvotes: 0

Views: 453

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Oracle creates the index in all cases for unique (and primary key constraints).

The using index statement allows you to either:

  • Specify the name of the index in the constraint statement.
  • Using an existing index for a new constraint.

In fact, I don't find either of these particularly useful in practice. If you don't given an explicit index, Oracle creates the appropriate index for the constraint.

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142720

If columns used for constraint are already indexed, Oracle will use that index.

Otherwise, it'll create one on its own.

In any case, there will be a unique index, and rows will be constrained by it.

Upvotes: 3

Related Questions