CODEWITHSUNDEEP
sqloracle-databaseddl
Gandalf
Gandalf

Reputation: 9855

Oracle IDENTITY column versus PRIMARY KEY

Having trouble finding anything definite about whether it's needed to specify that an IDENTITY column as PRIMARY KEY in Oracle 12.2c. Does an IDENTITY column automatically create an index, like a PK? Is it just being redudant? I do believe you can have an IDENTITY column and separate PK, though we are not doing that.

ID NUMBER AS IDENTITY PRIMARY KEY == ID NUMBER AS IDENTITY ?

Upvotes: 4

Views: 3540

Answers (3)

William Robertson
William Robertson

Reputation: 16001

Does an IDENTITY column automatically create an index, like a PK?

No. An identity column is just a column auto-populated with a sequentially generated number. You can use it however you want, but the typical use is as a synthetic primary key.

Is it just being redundant?

No.

I do believe you can have an IDENTITY column and separate PK

Yes, you can.

though we are not doing that.

Fine, if you mean you are not having a separate PK column in addition to the identity column. Defining a PK constraint over the identity column would be a good idea.

Upvotes: 3

alexherm
alexherm

Reputation: 1362

An IDENTITY column can be and often is useful as primary key, but it doesn't have to be.

The identity column is very useful for the surrogate primary key column. When you insert a new row into the identity column, Oracle auto-generates and insert a sequential value into the column.

https://www.oracletutorial.com/oracle-basics/oracle-identity-column/

Upvotes: 1

Alex Yu
Alex Yu

Reputation: 3537

It's a common mistake to mix logical and physical organization of data.

You successfully mixed 3 orthogonal concepts:

  • logical: PRIMARY KEY constraint
  • physical: INDEX
  • automatic value generation: IDENTITY column

Does an IDENTITY column automatically create an index, like a PK? Is it just being redudant?

Those questions are very version dependent. IDENTITY itself was introduced in Oracle 12.x.

I do believe you can have an IDENTITY column and separate PK, though we are not doing that.

You are correct here.

Auto value generation, logical constraint and physical data organization are orthogonal to each other.

Upvotes: 1

Related Questions