Differences and relationships between Oracle primary key and unique key

Both primary key and unique key are uniqueness constraints. But there is a big difference between the two:

1. One or more columns of the primary key must be not null,

If this column is set to NULL during table creation, the column is automatically changed to not null when the primary key is added.

The column with the unique key constraint can beNullThis is the biggest difference between primary key and unique key.

2. A table can only have one primary key (single or multiple columns, multiple primary keys are called joint primary keys), but multiple unique keys can exist.

Instance 1:

Create table t (c1 number (2), c2 date, c3 varchar2 (5), c4Int);

Desc t;

Name Type Nullable Default Comments

--------------------------------------

C1 NUMBER (2) Y

C2 DATE Y

C3 VARCHAR2 (5) Y

C4 INTEGER Y

//

Instance 2: Add a primary key

Alter table t add constraint t_pk primary key (c1, c2 );

Desc t;

Name Type Nullable Default Comments

--------------------------------------

C1 NUMBER (2)

C2 DATE

C3 VARCHAR2 (5) Y

C4 INTEGER Y

We can see that after the c1 and c2 columns are set as the joint primary key, they become notNull;

If a primary key is specified during table creation, the primary key column is not by default.Null.

//

If we add a primary key, we will get an error:

Alter table t add constraint t_pk_2 primary key (c3, c4)

ORA-02260: table can have only one primary key

//

Instance 3: Add a unique key

Alter table t add constraint unique_key_t unique (c3, c4 );

Instance 4: add data

Insert into t (c1, c2, c3, c4)

Values (10, sysdate,'Abc', 3 );

1 row inserted

//

Insert into t (c1, c2, c3, c4)

Values (11, sysdate,'Abc', 3 );

ORA-00001: unique constraint (SCOTT. UNIQUE_KEY_T) violated

We can see that the second data entry violates the unique key constraint just created;

Delete unique_key_t and add it successfully.

Instance 5: Delete the unique key

Alter table t drop constraint unique_key_t;

Insert into t (c1, c2, c3, c4)

Values (11, sysdate,'Abc', 3 );

1 row inserted

//

Instance 6: Delete the primary key

Alter table t drop constraint t_pk;

Table altered

//

Desc t;

Name Type Nullable Default Comments

--------------------------------------

C1 NUMBER (2) Y

C2 DATE Y

C3 VARCHAR2 (5) Y

C4 INTEGER Y

After the primary key constraint is deleted, the original default values of column c1 and column c2 are restored.Null.

//

3. In fact, the primary key is also a unique key, and the column not restricted by the primary keyNullAnd cannot be repeated.

Instance 7:

Truncate table t;

Table truncated

//

Alter table t add constraint t_pk primary key (c1, c2 );

Table altered

//

Insert into t (c1, c2, c3, c4)

Values (1, to_date ('2017-01-01','Yyyy-mm-dd'),'Csdn', 10 );

1 row inserted

//

Insert into t (c3, c4)

Values ('China', 1 );

ORA-01400: cannot insert NULL ("SCOTT"."T"."C1")

//

Insert into t (c1, c2, c3, c4)

Values (1, to_date ('2017-01-01','Yyyy-mm-dd'),'Csdn', 10 );

ORA-00001: unique constraint (SCOTT. T_PK) violated

Here, we can see that the primary key t_pk becomes a unique constraint, proving that the primary key constraint is also the only constraint.