Difference and connection between Oracleprimarykey and uniquekey

Both primarykey and uniquekey are uniqueness constraints, which are used to restrict the insertion of the same field. But there is a big difference between the two: 1. one or more columns of primarykey must be NOTNULL. If this column is set to NULL during table creation, the column is automatically changed to NOTNULL when PRIMARYKEY is added. The uniquekey constraint can be null, which is the primarykey.

Both primarykey and uniquekey are uniqueness constraints, which are used to restrict the insertion of the same field. But there is a big difference between the two: 1. one or more columns of primarykey must be NOTNULL. If this column is set to NULL during table creation, the column is automatically changed to NOTNULL when PRIMARYKEY is added. The uniquekey constraint can be null, which is the primarykey.

Both primarykey and uniquekey are uniqueness constraints, which are used to restrict the value insertion of the same field. But there is a big difference between the two:

1. One or more columns of primarykey must be NOTNULL,
If this column is set to NULL during table creation, the column is automatically changed to NOTNULL when PRIMARYKEY is added.
The uniquekey constraint can be null, which is the biggest difference between primarykey and uniquekey.
2. A table can only have one primarykey (single or multiple columns, multiple primary keys are called joint primary keys), but multiple uniquekeys can exist.
Instance 1:
Createtablet (c1number (2), c2date, c3varchar2 (5), c4int );
Desct;
Nametypenullabledefacommcomments
-------------
C1NUMBER (2) Y
C2DATEY
C3VARCHAR2 (5) Y
C4INTEGERY
//
Instance 2: Add primarykey
Altertabletaddconstraintt_pkprimarykey (c1, c2 );
Desct;
Nametypenullabledefacommcomments
-------------
C1NUMBER (2)
C2DATE
C3VARCHAR2 (5) Y
C4INTEGERY
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 notnull by default.
//
If we add a primarykey, we will get an error:
Altertabletaddconstraintt_pk_2primarykey (c3, c4)
ORA-02260: tablecanhaveonlyoneprimarykey.
//
Instance 3: Add a uniquekey
Altertabletaddconstraintunique_key_tunique (c3, c4 );
Instance 4: add data
Insertintot (c1, c2, c3, c4)
Values (10, sysdate, 'abc', 3 );
1 rowinserted
//
Insertintot (c1, c2, c3, c4)
Values (11, sysdate, 'abc', 3 );
ORA-00001: uniqueconstraint (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 uniquekey
Altertabletdropconstraintunique_key_t;
Insertintot (c1, c2, c3, c4)
Values (11, sysdate, 'abc', 3 );
1 rowinserted
//
Instance 6: delete primarykey
Altertabletdropconstraintt_pk;
Tablealtered
//
Desct;
Nametypenullabledefacommcomments
-------------
C1NUMBER (2) Y
C2DATEY
C3VARCHAR2 (5) Y
C4INTEGERY
After the primary key constraint is deleted, column c1 and column c2 restores the original default value null.
//
3. In fact, primarykey is also a uniquekey. The column notnull bound by primarykey cannot be repeated.
Instance 7:
Truncatetablet;
Tabletruncated
//
Altertabletaddconstraintt_pkprimarykey (c1, c2 );
Tablealtered
//
Insertintot (c1, c2, c3, c4)
Values (1, to_date ('1970-01-01 ', 'yyyy-mm-dd'), 'csdn', 10 );
1 rowinserted
//
Insertintot (c3, c4)
Values ('China', 1 );
ORA-01400: cannotinsertNULLinto ("SCOTT". "T". "C1 ″)
//
Insertintot (c1, c2, c3, c4)
Values (1, to_date ('1970-01-01 ', 'yyyy-mm-dd'), 'csdn', 10 );
ORA-00001: uniqueconstraint (SCOTT. T_PK) violated
Here, we can see that primarykeyt_pk becomes a unique constraint, proving that the primary key constraint is also a unique constraint.