The difference between Primary key and unique key

Both the Primary key and the unique key are unique constraints. But there's a big difference:

1, Primary key 1 or more columns must be NOT NULL, if column is null, when the Primary key is added, the columns are automatically changed to NOT NULL. The unique KEY does not have this requirement on the column.

2. A table can have only one primary key, but there may be multiple unique keys.

Here's a test note:

Sql> CREATE TABLE T (a int,b int,c int,d int);

Table created.

Sql> desc T
Name Null? Type
----------------------------------------- -------- -----------

A Number (38)
B Number (38)
C Number (38)
D Number (38)

Sql> ALTER TABLE t add constraint pk_t primary key (A, b);

Table altered.

Sql> desc T
Name Null? Type
----------------------------------------- -------- ----------------

A not NULL number (38)
B not NULL number (38)
C Number (38)
D Number (38)

You can see that both A and B two columns are automatically changed to not NULL

Sql> ALTER TABLE T Modify (a int null);
ALTER TABLE T Modify (a int null)
*
ERROR at line 1:
Ora-01451:column to is modified to null cannot is modified to null
As you can see, column A is not allowed to change to null

Sql> ALTER TABLE t drop constraint pk_t;

Table altered.

Sql> ALTER TABLE t add constraint uk_t_1 unique (a, b);

Table altered.

Sql> desc T
Name Null? Type
----------------------------------------- -------- -----------

A Number (38)
B Number (38)
C Number (38)
D Number (38)

We see that column a goes back to null.

Notice that when you delete a primary key, the nullable of the column returns to its original state. If, after the primary key is created, the primary key column that was originally NULL is explicitly set to NOT NULL, it is still not NULL after the primary key is deleted. For example, after creating the primary key, do the following to see:

Sql> ALTER TABLE t modify (b int not null);

Table altered.

Sql> ALTER TABLE t drop constraint pk_t;

Table altered.

Sql> desc T
Name Null? Type
----------------------------------------- -------- ----------

A Number (38)
B not NULL number (38)
C Number (38)
D Number (38)

Then do the following experiments:

sql> drop table t;

Table dropped.

Sql> CREATE TABLE T (a int,b int,c int,d int);

Table created.

Sql> ALTER TABLE t add constraint uk_t_1 unique (a, b);

Table altered.

Sql> ALTER TABLE t add constraint uk_t_2 unique (c,d);

Table altered.

You can see the addition of two unique keys. See if you can add two primary keys:

Sql> ALTER TABLE t add constraint pk_t primary key (c);

Table altered.

Sql> ALTER TABLE t add constraint pk1_t primary key (d);
ALTER TABLE t add constraint pk1_t primary key (d)
*
ERROR at line 1:
Ora-02260:table can has only one primary key
From this you can see that a table can have only one primary key.

Sql> ALTER TABLE t drop constraint pk_t;

Table altered.

sql> INSERT into t (A, B) values (null,null);

1 row created.

Sql>/

1 row created.

sql> INSERT into t (A, B) values (null,1);

1 row created.

Sql>/
INSERT into t (A, B) values (null,1)
*
ERROR at line 1:
Ora-00001:unique constraint (sys.uk_t_1) violated

sql> INSERT into t (A, B) values (1,null);

1 row created.

Sql>/
INSERT into t (A, B) values (1,null)
*
ERROR at line 1:
Ora-00001:unique constraint (sys.uk_t_1) violated

Primary KEY and unique key constraints are implemented through a reference index, and if the inserted values are null, then according to the principle of the index, all null values are not recorded on the index, so when you insert a full null value, you can have duplicates, while others cannot insert duplicate values.

The difference between Primary key and unique key