What is the difference between primary key and unique index

Difference: 1. The primary key is a constraint, and the unique index is an index. 2. After the primary key is created, it must contain a unique index. The unique index is not necessarily the primary key. 3. The unique index column allows null values, but the primary key column does not allow null values. 4. The primary key can be referenced as a foreign key by other tables, but the unique index cannot.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The difference between primary key index and unique index

• The primary key is a constraint, and the unique index is an index. The two are essentially the same 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 a null 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.

Example:

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

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

--Create separately Primary key and unique index, the syntax is different

ALTER TABLE test ADD CONSTRAINT test_PrimaryKey PRIMARY KEY (PrimaryKey);

CREATE UNIQUE INDEX test_UniqueKey ON test (UniqueKey);

--You can see two index names in USER_INDEXES

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

--In USER_IND_COLUMNS You can see two index field names

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

--Only the primary key 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 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');

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

ALTER TABLE test MODIFY UniqueKey NOT NULL;

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

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

-- Only primary key constraint field names and non-null constraints can be seen in USER_CONS_COLUMNS Field name

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')

For more programming-related knowledge, please visit: Programming Teaching! !

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!