Study Notes on Oracle Database Tables

Overview of Database Tables in Oracle (19c)

  • Purpose: Creating new tables for temporary or permanent use

  • Importance of understanding the context of tables within the database

Types of Database Objects

  • Definition: Objects owned by a schema (or user) that can be created and manipulated using SQL statements.

  • Common Types of Database Objects:

    • Tables

    • Indexes

    • Views

    • Synonyms

Naming Rules for Tables and Columns

  • Valid Name Length: Up to 128 characters

  • Allowed Characters:

    • Letters

    • Digits

    • Special characters: underscore (_), dollar sign ($), and pound sign (#)

  • Reserved Keywords: Cannot use names such as 'WHERE' or 'SELECT' for tables or columns to avoid confusion.

  • Case Sensitivity: Object names do not have to be uppercase; SQL processor automatically maps them to uppercase for storage.

Supported Data Types in Oracle Tables

  • Number Data Type:

    • Stores numeric data with precision of up to 38 digits.

  • VARCHAR2 Data Type:

    • Stores character strings with a maximum length of 30,767 characters.

  • Date Data Type:

    • Stores date and time data, precise to the nearest second.

    • If time is not supplied, set to zero (midnight).

  • Usage Context:

    • Numeric, character, and date data types are the most common.

    • Extended data types are essential for events requiring high-precision timestamps or timezone considerations.

Create Table Syntax

  • Basic Form:

    • CREATE TABLE table_name (column_name data_type, ...);

    • Schema prefix can be added if creating in a different schema: schema_name.table_name.

  • Default Clause:

    • Used to automatically populate a column if no value is included in the insert statement.

  • Example:

  CREATE TABLE hire_dates (
      ID NUMBER(8) NOT NULL,
      hire_date DATE DEFAULT sysdate
  );

Integrity Constraints

  • Definition: Rules that restrict the values allowed in columns either independently or through relationships with other tables.

  • Types of Constraints:

    • NOT NULL Constraint: Ensures a column will never contain NULL values.

    • Unique Constraint: Ensures all entries in a column are unique.

    • Primary Key Constraint: Enforces uniqueness and identifies records in a table.

    • Check Constraint: Conditional expression that restricts admissible values in a column (e.g., a salary must be greater than zero).

    • Foreign Key Constraint: Ensures values in a column correspond to values in a column of another table.

  • Flexibility: Constraints can be added, dropped, or modified.

Viewing Table Definitions and Constraints

  • Using SQL Developer:

    1. Expand the HR DB connection.

    2. Navigate to Database Objects > Tables.

    3. Select the desired table (e.g., departments).

    4. Check the Columns tab for details on column names, data types, NULL constraints, default values.

    5. Check the Constraints tab for primary and foreign key constraints.

  • Using SQL CL Command Tool:

    • Use the DESCRIBE table_name or DDL table_name commands to view table definitions and existing constraints.

Summary of Key Points

  • The CREATE TABLE statement is essential for establishing tables in Oracle databases.

  • Keep column name length under 128 characters for simplicity.

  • The most commonly used data types include VARCHAR2, NUMBER, and DATE.

  • Use constraints effectively to ensure data integrity and enforce structure on the data being managed.