The difference between primary key and unique

A field that defines a unique constraint cannot contain duplicate values, and you can define a unique constraint for one or more fields. Therefore, aUNIQUE can be defined at the field level or at the table level, and the field on the uniqued constraint can contain null values. Oracle automatically establishes a unique index and a NOT NULL constraint for the field with the PRIMARY key constraint (the primary Code field), which can be indexed when the PRIMARY key constraint is defined;

The uniqued is nullable and can be defined in one or more fields in a table;PRIMARY key is not nullable and can be defined as a joint primary key in a table;

Simply put, theprimary key = unique + NOT NULL

Unique is unique when you need to qualify one of your table fields for each value that is unique and has no duplicate values. For example, if you have a person table and you have an ID column in the table, you can specify that the field is unique. From a technical point of view, there are many similarities between the Primary key and the unique key. But there are still the following differences:

The domain/domain group that is the primary key cannot be null, and the unique key can.

There can be only one primary key in a table, and multiple unique keys can exist at the same time.

The larger difference is in the logical design. Primary key is generally used as a record identifier in the logical design, which is the original intention of setting the Primary key, and the unique key is only to guarantee the uniqueness of the domain/domain group.

There are two kinds of constraints in Oracle's constraint, the uniqueness of the column ――unique and primary key, but there are differences:

1, the unique key requires column unique, but does not include null fields, that is, the constraints of the column can be empty and only require the value in the column in addition to null not repeat;

2, primary Key also requires column unique, but also limit the value of the field can not be null, equivalent to primary key=unique + NOT NULL.

Creating a primary key and a unique key will create a unique index accordingly.

Syntax for 0primary key: ALTER TABLE table name add constraint key Name primary key (columns);

Syntax for the unique key: ALTER TABLE table name add constraint key name unique (columns);

A table can have only one primary key, but there are many unique, and unique can be null, such as the employee's phone number is usually unique, because the phone number is definitely unique, but some employees may not have a phone.

The primary key is definitely unique, but the only one is not necessarily the primary key;

Don't always confuse a unique index with a unique constraint

1, PRIMARY key = unique + NOT NULL

2, the unique constraint and the primary key is the same as the scope of constraints, and can be used as a reference to foreign keys, the difference is that a table can only have a primary key

3. The creation of primary keys and unique constraints depends on the index, and Oracle automatically establishes a unique index if the primary KEY or UNIQUE constraint is created without the already built index available.