Creating a Heap-Organized Table- Tables and Constraints

To create a heap-organized table, you use the CREATE TABLE statement and data types and lengths associated with the columns. The Oracle default table type is heap organized. The term heap means that the data are not stored in a specific order in the table; instead, they are a heap of data.

If you do not specify a tablespace, then the table is created in the default permanent tablespace of the user that creates the table. Allowing the table to be created in the default permanent tablespace is fine for a few small test tables. For anything more sophisticated, you should explicitly specify the tablespace in which you want tables created.

Usually, when you create a table, you should also specify constraints, such as the primary key. The following code shows the most common features you use when creating a table. This DDL defines primary keys, foreign keys, tablespace information, and comments.

When creating a table, the table inherits its space properties from the tablespace in which it is created and does not need to be specified.

This simplifies administration and maintenance. If you have tables that require different physical space properties, then you can create separate tablespaces to hold tables with differing needs.

For instance, you might create an HR_DATA_LARGE tablespace with extent sizes of 16MB and an HR_ DATA_SMALL tablespace with extent sizes of 128KB and choose where a table is created based on its storage requirements.
See Chapter 4 for details regarding the creation of tablespaces.

Table Recommendations

Set standards for naming the database objects. This can be based on the application and simplifies maintenance.
Use the right data type for the data. If storing a date, use a DATE; if storing a number, use a NUMBER. It seems simple, but it is worth the additional step of loading the data to convert the data to the correct data type instead of storing it as is. If it comes in as VARCHAR2, and should be DATE or JSON change the data type to match the data type.

Leverage the data type length and precision if there are business rules and application rules that need to be followed.
Use a primary key that is numeric. You can create a surrogate key with the identity column if the data does not have a unique numeric identifier. You can use a sequence possibly with a trigger to generate the number for the key or you can use an identity column that autoincrements.

Create audit-type columns such as CREATE_DT, UPDATE_DT, and CHANGE_USER, which can be automatically populated with default values or triggers.
Use check constraints when appropriate including NOT NULL.