How to create a table in Oracle SQL

Although, as routine as it might seem, understanding the context of table design, data types, constraints, and performance considerations can be quite a challenge. In this article, we’ll explore the step-by-step process of creating a table in Oracle SQL. But before diving in, let’s first explore why tables are essential to databases and why they’re important.

What are tables in databases and why are they important?

Tables are structured formats used in databases to store and organize data in rows and columns. Within a table, each row represents a unique data record, and each column defines a specific attribute for that data. For instance, in a customer table, each row would represent an individual customer, and the email column would store the email address for each customer.

What’s even more interesting is that databases always have multiple tables because the data in a table is very specific. The customer table would only have data about our customers and not the organization’s financial transactions. Thus, to gain a broader and more insightful view of your data, relationships are created between via what we call data models. These data models define how your tables connect and interact within your database.

TL;DR: 

• Structured and organized way to present complex data.
• Facilitate efficient data storage and analysis.
• Ensure data consistency, accuracy, and integrity through proper constraints and relationships.
• Establish relationships with your database.

Now, let’s take a quick look at how to create a table in Oracle SQL.

Understanding the CREATE TABLE statement

The CREATE TABLE statement is used to create a new table within a relational database. This statement applies across all the various forms of SQL, not just Oracle SQL.

Basic syntax

The basic syntax for the CREATE TABLE statement is:

CREATE TABLE schema_name.table_name (

    column_1 data_type column_constraint,

    column_2 data_type column_constraint

 );

Key elements of a table definition

The CREATE TABLE statement syntax comes with the following arguments:

• Column Names: Each column in the table must have a unique name. For example column_1.
• Data Types: The data type for each column must be defined to specify the kind of data it will store. For example VARCHAR2.
• Constraints: Specify rules to ensure integrity of the data. For example, PRIMARY KEY, NOT NULL, or UNIQUE.

Data types in Oracle SQL and why they are important

Like other databases, Oracle SQL uses data types to define the kind of values a column can hold. Here are the most common data types:

• Numeric: These data types are used to store numeric values either with high precision, whole, or approximate numbers. They’re NUMBER, INTEGER, and FLOAT.
• Strings: These data types are used to store characters either as fixed length or variable length. They include CHAR, NCHAR, VARCHAR2, and NVARCHAR2.
• Object: These data types are used for large objects; BLOB for binary data and CLOB for character data.
• Date and Time: These data types are used to store date and time information. They’re DATE and TIMESTAMP.
• RAW: The RAW data type is used for variable-length binary data.

Choosing the appropriate data type

Data types are important because they:

• Ensure Data Validation: This ensures that only valid data is entered. For example, numeric data can’t be entered in a date column.
• Maintain Data Integrity: Because of constraints, consistency and accuracy can be enforced. This way, users can only enter integers for numeric columns.
• Optimize Storage Efficiency: Data types ensure that the right amount of space is allocated for each type of data, thus ensuring efficient use of storage.
• Support Relationships and Management: Data types play a role in table relationships, making database schema easier to understand, scale, and manage.
• Enhance Query Performance: Data is processed and indexed much quicker and effectively.

Like other databases, Oracle SQL uses data types to define the kind of values a column can hold.

Defining primary and foreign keys

Earlier, we talked about constraints; PRIMARY and FOREIGN KEYS are a few examples of constraints. These keys are used to enforce integrity rules on the data within your Oracle database.

• The PRIMARY KEY is the primary column of your table and cannot contain NULL values.
• The FOREIGN KEY is a column in your table (the child table) that refers to the PRIMARY KEY in another table (parent table).

The relationships between tables in your databases, which is oftentimes a one-to-many relationship, are established through the PRIMARY KEY and FOREIGN KEY. The image below illustrates the schema design of a one-to-many relationship using the popular SQL Murder Mystery game as an example.

The key symbol shows the PRIMARY KEY in each table while the arrow → shows the FOREIGN KEY.

TL;DR: Schema design of the SQL Murder Mystery

• Each person can have one driver’s license (one-to-one relationship) and at least one income record (one-to-one relationship)

Person Table

• id is the PRIMARY KEY.
• license_id is a FOREIGN KEY that references the id in the drivers_license table
• ssn is a FOREIGN KEY that references the ssn in the income table

Drivers_license Table (has no FOREIGN KEY)

• id is the PRIMARY KEY.

Income Table (has no FOREIGN KEY)

• ssn is the PRIMARY KEY.

Thus, a column can be a PRIMARY KEY in one table and also a FOREIGN KEY in another. For example, the ssn column.

Adding constraints

Constraints help enforce integrity rules on the data within your table.

• NOT NULL: This ensures that there are no empty or NULL values within the column. This is very important in some industries like financial institutions where columns like address and identification number must be filled to ensure a successful Know Your Customer (KYC) and prevent fraud.
• UNIQUE: This ensures there are no duplicates within that column. For example, client identification numbers within a customer table must be unique. Duplicates in that column would be messy and could indicate fraudulent activity.
• CHECK: This is used for validation and to enforce that the column meets a specific condition. For example, ensuring only customers who have met the KYC requirements are moved to tier 1 customer category.

CREATE TABLE Customers (

    customer_id INT PRIMARY KEY,  

    name VARCHAR2(100) NOT NULL,   -- Name cannot be NULL

    email VARCHAR2(100) UNIQUE NOT NULL,  -- Email must be unique and cannot be NULL

    phone_number VARCHAR2(15) UNIQUE,  -- Phone number must be unique, but can be NULL

    kyc_status VARCHAR2(20) NOT NULL CHECK (kyc_status IN ('Completed', 'Pending', 'Not Submitted')),  -- KYC status must be one of the allowed values

    tier VARCHAR2(20) NOT NULL CHECK (tier IN ('Tier 1', 'Tier 2', 'Tier 3'))  -- Tier must be one of the allowed values

);

Constraints help enforce integrity rules on the data within your table.

How to create a table in Oracle SQL with examples

To create a new table in Oracle Database, you use the CREATE TABLE statement. However, before you can start, you need to have the following prerequisites:

• Oracle database management system: You can use Oracle Database 19c or Oracle Database 21c.
• Oracle SQL Developer, a graphical user interface (GUI) tool that allows you to interact with Oracle databases.

Now let’s create a basic customer table:

1. Launch Oracle software and connect to your database.
2. Open SQL Developer.
3. Write the CREATE TABLE statement along with the name of the table.
4. Within the CREATE statement:
   • Write the name of the columns.
   • Define the data type, size, and any constraints for each column.
5. Run the query.

CREATE TABLE Customers (

    customer_id INT PRIMARY KEY,    

    name VARCHAR2(100) NOT NULL,   

    email VARCHAR2(100) NOT NULL,    

    phone_number VARCHAR2(15),     

    registration_date DATE      

);

In the SQL script above,

• Customers is the name of our table.
• customer_id is the primary key of our table.
• Other columns are name, email, phone number, and registration date.
• The data types are also specified along with any constraints.

Managing tables in Oracle SQL

Managing tables includes tasks like modifying values, renaming tables, adding or deleting columns, altering table structures, and adding constraints.

Altering existing tables

• Add a New Column

To add a new column to the Customers table, you use the ADD statement.

ALTER TABLE Customers

ADD address VARCHAR2(255);

• Modify a column: This change the properties of an existing column

Use the MODIFY statement. For example, you can modify the phone_number VARCHAR2(15) to phone_number VARCHAR2(20).

ALTER TABLE Customers

MODIFY phone_number VARCHAR2(20);

• Rename a Column

ALTER TABLE Customers

RENAME TO ClientDetails;

• Drop a Column

ALTER TABLE Customers

DROP COLUMN phone_number;

• Add a Primary Key

ALTER TABLE Customers

ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);

• Drop a Primary Key

ALTER TABLE Customers

DROP CONSTRAINT pk_customer_id;

Dropping tables safely

While dropping tables in Oracle SQL is a one click action. Thus it should be done with caution, as this action permanently deletes the table and all of its data.

It is also advisable that you verify by taking a look using the SELECT statement before deleting it.

SELECT table_name

FROM user_tables

WHERE table_name = 'CUSTOMERS';

You can also preserve the data before dropping it by exporting the table to a file or temporary table.

CREATE TABLE customers_backup AS SELECT * FROM customers;

After verifying the data, you can safely drop the table using the DROP statement:

DROP TABLE customers;

Troubleshooting common issues

Here are some common issues you might encounter when creating a table:

• Assigning the wrong data types upon creating. For example, using VARCHAR2 for your date column.
• Neglecting to add constraints to your data. Data integrity may be compromised if constraints are not enforced.
• Using a column that will contain duplicate values, such as a name column, as your PRIMARY KEY.
• Not regularly updating your data. You can use triggers and scheduled jobs to ensure data consistency and integrity.

Best practices in table design

Here are five best practices you should adhere to, to ensure efficiency, maintainability, and consistency when creating tables:

1. Naming conventions

• Use clear, concise, descriptive names from your tables and columns. For example: customer, order, product, and email_address.
• Avoid spaces and special characters.
• Depending on your organization’s engineering style, follow either:
  • The Python style, which uses lowercase letters with underscores to separate words, like user_details.
  • The CamelCase style, which capitalizes the first letter of each word, like UserDetails.

2. Comments

Comments improve long-term maintainability and help anyone taking a look to understand your code.

You can add comments using the — symbol:

CREATE TABLE Products (

    product_id INT PRIMARY KEY,         -- Unique identifier for each product

    name VARCHAR2(100) NOT NULL

);

Or   /* and */ symbol:

CREATE TABLE Products (

    product_id INT PRIMARY KEY,     

/* this represent the unique identifier for each product */

    name VARCHAR2(100) NOT NULL

);

Alternatively, use the COMMENT statement to comment about a table or column into the data dictionary:

COMMENT ON TABLE Customers IS 'stores personal details and KYC status of customers';

3. Performance considerations

• Ensure that you are using the right data types for your columns for optimal storage and performance.
• Normalize tables to reduce redundancy. For example, instead of storing the customer name in both your Customer and Order table, consider having the name in the customer table and reference it as a customer_id in the Order table.

4. Ensuring data integrity with constraints

• Define your PRIMARY KEY when creating tables within your databases.
• Use FOREIGN KEYS to maintain data integrity between tables.
• Depending on the column, use either the NOT NULL, UNIQUE or CHECK constraints.

5. Get expert help

Work with automation and integration experts like Tricentis to navigate complex database structures, minimize risks, and design efficient database schema that can help you achieve business needs.

Final thoughts

Now that you’ve learned how to create a table in Oracle SQL, you’re one step closer to mastering database management. But why stop here? With Tricentis, you can streamline your database workflows—just as we’ve successfully done for countless other organizations with Oracle and Tricentis integration.

Learn more by reading all about the importance of integration testing for Oracle Applications and running without disruptions. Sign up for a free trial.

This post was written by Ifeanyi Benedict Iheagwara. Ifeanyi is a data analyst and Power Platform developer who is passionate about technical writing, contributing to open source organizations, and building communities. Ifeanyi writes about machine learning, data science, and DevOps, and enjoys contributing to open-source projects and the global ecosystem in any capacity.