CODEWITHSUNDEEP
databasedatabase-designoracle11grdbmsdatabase-schema
Kristofer Hoch
Kristofer Hoch

Reputation: 246

Request suggestions for defining a primary key in Oracle 11g

All,

I have read several other posts before posing my question to you.

I have a lot of programming/admin experience with other databases: MySql, MSSQL, PostGres, and the one which will not be named. I just don't have much experience with Oracle.

I was tasked with designing a few web-applications and supporting database tables. The tables were designed using an ER diagram, and sent to the development group for implementation. When they sent back the proposed table creation statements, I saw two things that seems wrong to me. The primary key is NUMBER(5) and the sequence set the MAXVALUE to 99999.

I would have expected that the MAXVALUE would be omitted in favor of NOMAXVALUE Primary key column be a NUMBER(*,0) or a LONG. Since I don't have much experience with Oracle table design, would you please offer up your advice?

Sincerely

Kristofer Hoch

Edit Thank you for the information on LONG. I'll make sure to use NUMBER, but I'm still unclear on the best way to define it: NUMBER, or NUMBER(*,0), or NUMBER(9), etc.

Upvotes: 1

Views: 928

Answers (2)

l_smith
l_smith

Reputation: 123

If it was me, I wouldn't specify the size of the number, but that may be more laziness (set it, forget it) than a good practice for design purposes.

But "long" would cause problems. In Oracle, "long" is a character data type that's being deprecated. It's not the same thing as the long data type for numbers in other languages/systems. It's tricky.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

I agree with you: since the column is to hold a surrogate key generated from a sequence, the only possible purpose of the 5 digit limit would be to restrict the total number of rows ever allowed in the table to under 100,000 - which would seem perverse. It certainly does not confer any performance or space efficiency advantages. Probably it is just the default of their ERD tool's DDL generator.

Do not use LONG: in Oracle that is an obsolete and deprecated way of storing large text strings (for which CLOB is now preferred).

Upvotes: 1

Related Questions