Unique Constraint, Primary Key and Unique Index

最新推荐文章于 2023-06-26 02:24:29 发布

原创最新推荐文章于 2023-06-26 02:24:29 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#database #manager #null #db2

DB2 专栏收录该内容

12 篇文章

订阅专栏

本文详细解释了数据库中三种确保数据唯一性的方法：唯一约束(UniqueConstraint)、主键(PrimaryKey)及唯一索引(UniqueIndex)。文章指出，唯一约束能够确保指定列没有重复值，并会在创建时自动生成唯一索引；主键除了具有唯一性还要求不能为空，并可参与外键约束；唯一索引同样可以强制数据唯一性，但允许存在NULL值且通常不用于外键约束。

Unique Constraint, Primary Key and Unique Index

1. Unique Constraint

Ensure no duplicate value exists in the columns for which unique constraint has been defined.

Regardless of when a unique constraint is defined, when it is created the DB2 Database Manager looks to see if an index for the columns the unique constraint refers to already exists. If so, that index is marked as being unique and system-required. If not, an appropriate index is created and marked as being unique and system-required. This index is then used to enforce uniqueness whenever new records are added to the column(s) the unique constraint was defined for.

2. Primary Key

Have Unique, NOT NULL and referential constraints.

3. Unique Index

Enforce uniqueness like Unique Constraint, but a Unique Index allows NULL columns and generally cannot be used in a referential constraint.