• A primary key is a constraint defined on one or more columns of a table that uniquely identifies each row.
  
  
• Primary key constraints enforce entity integrity by ensuring that each row in a table can be uniquely identified.
  
  
• The values of these columns combined ensure the uniqueness of each row in the table and cannot be duplicated.
  
  
• Primary key columns cannot contain null values. Oracle automatically applies NOT NULL constraints to all primary key columns.
  
  
• Each table can have only one primary key constraint, but it can span multiple columns (composite primary key).
  
  
• When you create a primary key constraint, Oracle automatically creates a unique index to enforce the constraint unless you specify an existing index to use.
  

1. Method 1: Named Constraint with CREATE TABLE
   This method creates a primary key constraint with an explicitly named constraint, which is considered best practice for maintainability and clarity.
   

   CREATE TABLE table_primary_key_1
   (
       id NUMBER(1,0)
   ,   CONSTRAINT cp_table_primary_key_1_id PRIMARY KEY (id) -- PRIMARY KEY
   );
   

   Note: If the primary key is a composite key of multiple columns, you must specify those columns separated by commas (column1, column2, …).
   
   Creating the primary key generates two objects: a primary key constraint and a unique index:
   

   • Check the existence of the primary key constraint in the data dictionary:
     

     SELECT constraint_name, constraint_type, table_name
     FROM user_constraints
     WHERE table_name = 'TABLE_PRIMARY_KEY_1';
     

     Query result:
     

     | CONSTRAINT_NAME           | CONSTRAINT_TYPE | TABLE_NAME          |
     +---------------------------+-----------------+---------------------+
     | CP_TABLE_PRIMARY_KEY_1_ID | P               | TABLE_PRIMARY_KEY_1 |
     

   • Check the existence of the unique index in the data dictionary:
     

     SELECT index_name, index_type, uniqueness
     FROM user_indexes
     WHERE table_name = 'TABLE_PRIMARY_KEY_1';
     

     Query result:
     

     | INDEX_NAME                | INDEX_TYPE | UNIQUENESS |
     +---------------------------+------------+------------+
     | CP_TABLE_PRIMARY_KEY_1_ID | NORMAL     | UNIQUE     |
     

   • If you try to drop this index directly, you will get an error because it's used to enforce the primary key constraint:
     

     DROP INDEX cp_table_primary_key_1_id;
     

     Query result:
     

     Error starting at line 1 in command:
     DROP INDEX CP_TABLE_PRIMARY_KEY_1_ID
     
     Error report:
     SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key.
     *Cause: user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key.
     *Action: drop the constraint instead of the index.
     

   • Dropping the primary key constraint will also drop the associated index:
     

     ALTER TABLE table_primary_key_1 DROP CONSTRAINT cp_table_primary_key_1_id;
     

     Check the existence of the unique index in the data dictionary:
     

     SELECT index_name
     FROM user_indexes
     WHERE table_name = 'TABLE_PRIMARY_KEY_1';
     

     Query result:
     

     | INDEX_NAME |
     +------------+
     

2. Method 2: Inline Definition with CREATE TABLE
   This method creates a primary key constraint without explicitly naming it. Oracle will automatically generate a system-generated constraint name.
   

   CREATE TABLE table_primary_key_2
   (
       id NUMBER(1,0)
   ,   PRIMARY KEY (id) -- PRIMARY KEY
   );
   

   The difference from the previous syntax is that no explicit name is given to the primary key constraint.
   In this case, Oracle automatically generates a constraint name in the format SYS_Cnnnnn.
   

   • Check the existence of the primary key constraint in the data dictionary:
     

     SELECT constraint_name, constraint_type, table_name
     FROM user_constraints
     WHERE table_name = 'TABLE_PRIMARY_KEY_2';
     

     Query result:
     

     | CONSTRAINT_NAME | CONSTRAINT_TYPE | TABLE_NAME          |
     +-----------------+-----------------+---------------------+
     | SYS_C007205     | P               | TABLE_PRIMARY_KEY_2 |
     

   • Check the existence of the unique index in the data dictionary:
     

     SELECT index_name, index_type, uniqueness
     FROM user_indexes
     WHERE table_name = 'TABLE_PRIMARY_KEY_2';
     

     Query result:
     

     | INDEX_NAME    | INDEX_TYPE | UNIQUENESS |
     +---------------+------------+------------+
     | SYS_C007205   | NORMAL     | UNIQUE     |
     

   Note: It is good practice to specify meaningful names for constraints to improve code maintainability and make debugging easier.
3. Method 3: Adding Primary Key with ALTER TABLE
   This method allows you to add a primary key constraint to an existing table. This is useful when you need to modify the table structure after creation.
   

   CREATE TABLE table_primary_key_3
   (
       id NUMBER(1,0)
   );
   

   Specify an explicit name for the constraint:
   

   ALTER TABLE table_primary_key_3 ADD CONSTRAINT cp_table_primary_key_3_id PRIMARY KEY (id); -- PRIMARY KEY
   

   Or allow Oracle to generate a system name:
   

   ALTER TABLE table_primary_key_3 ADD PRIMARY KEY (id); -- PRIMARY KEY
   

   Note: Before adding a primary key constraint to an existing table, ensure that the target column(s) contain no duplicate values and no NULL values, otherwise the operation will fail.
4. Method 4: Using Pre-existing Index
   This method allows you to use an existing index to enforce the primary key constraint, which can be beneficial for performance tuning and index management.
   

   CREATE TABLE table_primary_key_4
   (
       id NUMBER(1,0)
   );
   

   CREATE INDEX in_table_primary_key_4_id ON table_primary_key_4 (id);
   

   ALTER TABLE table_primary_key_4 ADD CONSTRAINT cp_table_primary_key_4_id PRIMARY KEY (id) USING INDEX in_table_primary_key_4_id; -- PRIMARY KEY
   

   Note that the primary key can use a non-unique index. Oracle will automatically make it function as a unique index for constraint enforcement.
   
   IMPORTANT NOTES:
   

   • As seen previously, you cannot drop the index once the primary key constraint is using it. You will get an error if you try to drop it:
     

     DROP INDEX in_table_primary_key_4_id;
     

     Error starting at line 1 in command:
     DROP INDEX IN_TABLE_PRIMARY_KEY_4_ID
     
     Error report:
     SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key.
     *Cause: user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key.
     *Action: drop the constraint instead of the index.
     

   • However, when using a pre-existing index, dropping the primary key constraint will not drop the index (unlike the previous methods):
     

     ALTER TABLE table_primary_key_4 DROP PRIMARY KEY;
     

     Check the existence of the index in the data dictionary:
     

     SELECT index_name
     FROM user_indexes
     WHERE table_name = 'TABLE_PRIMARY_KEY_4';
     

     Query result:
     

     | INDEX_NAME                |
     +---------------------------+
     | IN_TABLE_PRIMARY_KEY_4_ID |
     

     This behavior allows you to maintain the index for query performance even after removing the primary key constraint.
     

Disabling a primary key constraint temporarily removes the constraint enforcement without dropping the constraint definition. This is useful during data loading operations or maintenance tasks where you need to temporarily bypass constraint checking.

ALTER TABLE table_primary_key_1 DISABLE CONSTRAINT cp_table_primary_key_1_id;

Important: When a primary key constraint is disabled, Oracle does not enforce uniqueness or NOT NULL restrictions, but the underlying index may still exist depending on how the constraint was created. Disabling constraints should be done carefully as it can lead to data integrity issues.

Enabling a previously disabled primary key constraint restores constraint enforcement. Oracle will validate all existing data against the constraint rules when you enable it.

ALTER TABLE table_primary_key_1 ENABLE CONSTRAINT cp_table_primary_key_1_id;

Note: If the table contains data that violates the primary key constraint (duplicate values or NULL values in primary key columns), the enable operation will fail. You must clean up the data before enabling the constraint.

Dropping a primary key constraint permanently removes both the constraint definition and its enforcement. This operation also affects the associated index depending on how the constraint was created.

ALTER TABLE table_primary_key_1 DROP PRIMARY KEY;

Or specify the constraint name explicitly:

ALTER TABLE table_primary_key_1 DROP CONSTRAINT cp_table_primary_key_1_id;

Reminder: Dropping the primary key constraint also drops the associated index, except when the index was created separately before adding the constraint. This can significantly impact query performance if the index was being used by other queries.

While both primary key constraints and unique indexes enforce uniqueness, they have different behaviors regarding NULL values and constraint enforcement.

The values of each primary key column must be unique and can never be NULL. In fact, the primary key constraint implicitly enforces the NOT NULL constraint on each column in the primary key. If you try to insert a NULL value into a primary key column, you will get an error.

This is what differentiates a primary key from a UNIQUE constraint or a unique index. See the differences between UNIQUE constraint and unique index on the UNIQUE Constraints page.

Note: A primary key can technically be replaced, if truly needed, by creating a unique index and/or UNIQUE constraint on the primary key columns and adding NOT NULL constraints on those columns. However, using a proper primary key constraint is the recommended approach.

1. Primary Key Behavior with NULL Values
   Primary key constraints strictly prohibit NULL values in any of the constituent columns, even in composite primary keys.
   

   CREATE TABLE table_primary_key_5
   (
       id1 NUMBER(1,0)
   ,   id2 NUMBER(1,0)
   ,   CONSTRAINT cp_table_primary_key_5_id PRIMARY KEY (id1, id2) -- PRIMARY KEY
   );
   

   • Example 1: Attempting to insert NULL in the second column of a composite primary key:
     

     INSERT INTO table_primary_key_5 (id1, id2) VALUES (1, NULL);
     

     Query result:
     

     Error starting at line 1 in command:
     INSERT INTO TABLE_PRIMARY_KEY_5 (ID1, ID2) VALUES (1, NULL)
     
     Error report:
     SQL Error: ORA-01400: cannot insert NULL into ("TABLE_PRIMARY_KEY_5"."ID2")
     *Cause:
     *Action:
     

   • Example 2: Attempting to insert NULL in the first column of a composite primary key:
     

     INSERT INTO table_primary_key_5 (id1, id2) VALUES (NULL, 1);
     

     Query result:
     

     Error starting at line 1 in command:
     INSERT INTO TABLE_PRIMARY_KEY_5 (ID1, ID2) VALUES (NULL, 1);
     
     Error report:
     SQL Error: ORA-01400: cannot insert NULL into ("TABLE_PRIMARY_KEY_5"."ID1")
     *Cause:
     *Action:
     

   • Example 3: Attempting to insert NULL in both columns:
     

     INSERT INTO table_primary_key_5 (id1, id2) VALUES (NULL, NULL);
     

     Query result:
     

     Error starting at line 1 in command:
     INSERT INTO TABLE_PRIMARY_KEY_5 (ID1, ID2) VALUES (NULL, NULL);
     
     Error report:
     SQL Error: ORA-01400: cannot insert NULL into ("TABLE_PRIMARY_KEY_5"."ID1")
     *Cause:
     *Action:
     

2. Unique Index Behavior with NULL Values
   Unique indexes have different NULL handling behavior compared to primary key constraints. They allow NULL values but still enforce uniqueness for non-NULL combinations.
   

   CREATE TABLE table_pk_vs_unique_index
   (
       id1 NUMBER(1,0)
   ,   id2 NUMBER(1,0)
   );
   

   CREATE UNIQUE INDEX in_table_pk_vs_unique_index ON table_pk_vs_unique_index (id1, id2);
   

   • Example 1: Unique index allows one NULL value but prevents duplicates:
     

     INSERT INTO table_pk_vs_unique_index (id1, id2) VALUES (1, NULL); -- OK
     INSERT INTO table_pk_vs_unique_index (id1, id2) VALUES (1, NULL); -- ERROR
     

     Query result:
     

     1 rows inserted.
     
     Error starting at line 2 in command:
     INSERT INTO TABLE_PK_VS_UNIQUE_INDEX (ID1, ID2) VALUES (1, NULL)
     Error report:
     SQL Error: ORA-00001: unique constraint (HR.IN_TABLE_PK_VS_UNIQUE_INDEX) violated
     *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
                For Trusted Oracle configured in DBMS MAC mode, you may see
                this message if a duplicate entry exists at a different level.
     *Action:   Either remove the unique restriction or do not insert the key.
     

   • Example 2: Similar behavior with NULL in different column position:
     

     INSERT INTO table_pk_vs_unique_index (id1, id2) VALUES (NULL, 1); -- OK
     INSERT INTO table_pk_vs_unique_index (id1, id2) VALUES (NULL, 1); -- ERROR
     

     Query result:
     

     1 rows inserted.
     
     Error starting at line 2 in command:
     INSERT INTO TABLE_PK_VS_UNIQUE_INDEX (ID1, ID2) VALUES (NULL, 1)
     Error report:
     SQL Error: ORA-00001: unique constraint (HR.IN_TABLE_PK_VS_UNIQUE_INDEX) violated
     *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
                For Trusted Oracle configured in DBMS MAC mode, you may see
                this message if a duplicate entry exists at a different level.
     *Action:   Either remove the unique restriction or do not insert the key.
     

   • Example 3: Multiple rows with all NULL values are allowed:
     

     INSERT INTO table_pk_vs_unique_index (id1, id2) VALUES (NULL, NULL); -- OK
     INSERT INTO table_pk_vs_unique_index (id1, id2) VALUES (NULL, NULL); -- OK!
     

     Query result:
     

     1 rows inserted.
     1 rows inserted.
     

     In this last example, both inserts were successful. It's important to remember that a standard (B*Tree) index does not index NULL values when all indexed columns are NULL. Since the value of both columns composing the index is NULL, these rows are not indexed and therefore do not violate the uniqueness constraint.