Primary Key - Oracle

A primary key is a field or a set of fields in a database table that uniquely identify each record in the table. In Oracle, primary keys can be defined using the PRIMARY KEY constraint.

Syntax

The syntax for creating a primary key in Oracle is as follows:

CREATE TABLE table_name (
   column1 datatype [ NULL | NOT NULL ],
   column2 datatype [ NULL | NOT NULL ],
   ...
   column_n datatype [ NULL | NOT NULL ],
   CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ..., column_n)
);

Here, table_name is the name of the table, column1, column2, and column_n are the names of the columns that make up the primary key, and datatype is the data type of each column. The NULL or NOT NULL option specifies whether the column can contain null values or not, and constraint_name is the name given to the primary key constraint.

Example

Here is an example of how to create a table with a primary key in Oracle:

CREATE TABLE employees (
   employee_id   NUMBER(5),
   first_name    VARCHAR2(20),
   last_name     VARCHAR2(20),
   email         VARCHAR2(30),
   phone_number  VARCHAR2(15),
   hire_date     DATE,
   job_id        VARCHAR2(10),
   salary        NUMBER(10,2),
   commission_pct NUMBER(2,2),
   manager_id    NUMBER(5),
   department_id NUMBER(3),
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

In this example, we define a primary key constraint called employees_pk on the employee_id column.

Output

The output of creating a table with a primary key in Oracle is the successful creation of the table and the associated primary key constraint.

Explanation

In the above example, we define a table called employees with various columns. We then define a primary key constraint called employees_pk on the employee_id column, which ensures that each employee_id value in the table is unique.

Use

Primary keys are used in database tables to ensure that each record can be uniquely identified. They provide a way to enforce data integrity by preventing duplicate records from being inserted into the table. Primary keys are used in conjunction with foreign keys to establish relationships between tables.

Important Points

• A primary key is a field or a set of fields in a database table that uniquely identify each record in the table.
• Primary keys can be defined using the PRIMARY KEY constraint in Oracle.
• Primary keys are used in conjunction with foreign keys to establish relationships between tables.
• Primary keys help to enforce data integrity by preventing duplicate records from being inserted into the table.

Summary

In summary, a primary key is a field or a set of fields in a database table that uniquely identify each record in the table. In Oracle, primary keys can be defined using the PRIMARY KEY constraint. Primary keys are an important part of database design and help to ensure data integrity by preventing duplicate records from being inserted into the table.