What is the difference between primary key and unique index?

The difference between primary key and unique index

--Difference

The primary key is a constraint, and the unique index is an index. The two are essentially The above is different.
After the primary key is created, it must contain a unique index. The unique index is not necessarily the primary key.
Unique index columns allow null values, while primary key columns do not allow null values.
When the primary key column is created, it defaults to null value + unique index.
The primary key can be referenced as a foreign key by other tables, but the unique index cannot.
A table can only create one primary key at most, but multiple unique indexes can be created.
Primary keys are more suitable for unique identifiers that are not easy to change, such as auto-increment columns, ID numbers, etc.
In RBO mode, the execution plan priority of the primary key is higher than that of the unique index. Both can improve the speed of queries.

-- Create a table containing only the primary key and the unique index

CREATE TABLE test
(PrimaryKey VARCHAR2(20),
  UniqueKey  VARCHAR2(20)
);

-- Create the primary key and the unique index respectively, the syntax is different

ALTER TABLE test ADD CONSTRAINT test_PrimaryKey PRIMARY KEY (PrimaryKey);
CREATE UNIQUE INDEX test_UniqueKey ON test (UniqueKey);

-- Can be used in USER_INDEXES See two index names

SELECT table_name,table_type,index_name,index_type,uniqueness
  FROM USER_INDEXES
  WHERE TABLE_NAME='TEST';

--You can see two index field names in USER_IND_COLUMNS

SELECT table_name,index_name,column_name,column_position
  FROM USER_IND_COLUMNS
  WHERE TABLE_NAME='TEST';

--Only the primary key constraint names can be seen in USER_CONSTRAINTS

SELECT table_name,constraint_name,constraint_type
  FROM USER_CONSTRAINTS
  WHERE TABLE_NAME='TEST';

--Only the primary key constraint field names can be seen on USER_CONS_COLUMNS

SELECT table_name,constraint_name,column_name,position
  FROM USER_CONS_COLUMNS
  WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME                          
   FROM USER_CONSTRAINTS                            
   WHERE TABLE_NAME='TEST');

-- Add a non-null constraint to the unique index

ALTER TABLE test MODIFY UniqueKey NOT NULL;

-- Only the primary key constraint name and non-null constraint name can be seen in USER_CONSTRAINTS

SELECT table_name,constraint_name,constraint_type

  FROM USER_CONSTRAINTS
  WHERE TABLE_NAME='TEST'

--Only the primary key constraint field name and the non-null constraint field name can be seen in USER_CONS_COLUMNS

SELECT table_name,constraint_name,column_name,position
  FROM USER_CONS_COLUMNS
  WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME                             
  FROM USER_CONSTRAINTS                            
  WHERE TABLE_NAME='TEST')

The above is the detailed content of What is the difference between primary key and unique index?. For more information, please follow other related articles on the PHP Chinese website!