How to Create a Table in Oracle Using PRIMARY KEY?

Oracle database is known as the most popular database around the globe that stores and manages data in the form of relational tables. Table creation is a basic task for anyone working with the database. While creating a table, a primary key is an essential component as it uniquely identifies each record in the table and aids in improving query performance.

This post will discuss the following content:

• What is the PRIMARY KEY in the Oracle Table?
• Create an Oracle Table with a PRIMARY KEY
• Create an Oracle Table with a Unique PRIMARY KEY Constraint Name
• Create an Oracle Table with Composite PRIMARY KEY

What is the PRIMARY KEY in the Oracle Table?

In Oracle, a PRIMARY KEY is a column or columns that uniquely identify every row of a table. It guarantees that every row in a table has a distinct identifier which simplifies the process of retrieving data and linking tables.

Oracle automatically creates an index key when a primary key is defined on a table, which improves the performance of queries. Moreover, this column cannot store null values that ensure uniqueness and data integrity.

Create an Oracle Table with a PRIMARY KEY

To create a table named “Mannequins” with a PRIMARY KEY column, run this command:

The above command creates a table with three columns:

• The “mannequin_id” is a unique primary key column with the data type “NUMBER” and cannot store NULL values.
• The “mannequin_type” is a column with the data type “VARCHAR2” and a maximum length of ten characters.
• The “mannequin_detail” is a column with the data type “VARCHAR2” and a maximum length of fifty characters.

Output

The output displayed the success message after the creation of the table.

Note: When a PRIMARY KEY is created, Oracle generates a default name for its PRIMARY KEY constraint to identify it. However, you can define the primary key constraint by yourself.

Create an Oracle Table with a Unique PRIMARY KEY Constraint Name

To define a meaningful name for PRIMARY KEY CONSTRAINT, utilize the “CONSTRAINT” keyword. For instance, type the below command to create a new table named “Workers” with the PRIMARY KEY CONSTRAINT name and click on the “Execute” button:

The command given above will create a new table named “Workers” with four columns:

• The “worker_id” is a primary key column with the data type “NUMBER” and primary key constraint name “pk_worker”.
• The “worker_name” is a column with the data type “VARCHAR2” and a maximum length of thirty characters.
• The “worker_phone” is a column with the data type “NUMBER” and a maximum length of twenty digits.
• The “worker_mail” is a column with the data type “VARCHAR2” and a maximum length of fifty characters.

Output

The output depicts that the table is created successfully.

Create an Oracle Table with Composite PRIMARY KEY

In Oracle, the user can also create a table with a composite PRIMARY KEY which means that the PRIMARY KEY will be composed of more than one column. Execute the command provided below to create a new table named “Consumers” with a composite PRIMARY KEY:

The above code will create a table “Consumers” with four columns and a composite PRIMARY KEY containing “consumer_firstname” and “consumer_lastname” columns:

• The “consumer_id” is a column with the data type “NUMBER”.
• The “consumer_firstname” column is a part of PRIMARY KEY with the data type “VARCHAR2” and a maximum length of thirty characters.
• The “consumer_lastname” column is a part of PRIMARY KEY with the data type “VARCHAR2” and a maximum length of thirty characters.
• The “consumer_mail” is a column with the data type “VARCHAR2” and a maximum length of thirty characters.
• The PRIMARY KEY constraint name is “pk_consumer”.

Output

The output shows the success message after creating a new table with the composite primary key.

Conclusion

To create a table with a PRIMARY KEY, use the “PRIMARY KEY” keyword while defining a column in the “CREATE Table” statement. When a PRIMARY KEY is created, Oracle generates a default name for its PRIMARY KEY constraint to identify it. However, the users can define their own PRIMARY KEY constraint using the “CONSTRAINT” keyword. Moreover, Oracle also provides the facility of creating a composite PRIMARY KEY.