MySQL PRIMARY KEY与UNIQUE约束 [英] MySQL PRIMARY KEY vs UNIQUE constraints
问题描述
根据MySQL文档，PRIMARY KEY约束和UNIQUE约束之间的区别在于PK约束不允许NULL值，而UQ约束允许NULL值.在MySQL表中，可以在同一列中为一个创建PK和UQ！
According to the MySQL documentation, the difference between PRIMARY KEY constraint and UNIQUE constraint is that PK constraint DOES NOT allow NULL values while UQ constraint DOES allow NULL values. In MySQL table one can create both PK and UQ for one the same column!
- 为已经具有PK约束的列创建UNIQUE约束有什么意义或原因！?
- 为什么MySQL允许为已经具有PK约束的列创建唯一约束?
推荐答案
作为序言，请注意主键不必是单个列:它可以由多个列组成:这称为复合键.还要注意，并不是每个表都具有AUTO_INCREMENT/IDENTITY列，并且您仍然可以在复合键内的单个列上具有UNIQUE约束.
As a preface, note that a Primary Key does not need to be a single column: it can be comprised of multiple columns: this is known as a Composite Key. Also note that not every table has an AUTO_INCREMENT/IDENTITY column at all, and you can have a UNIQUE constraint on a single column inside a composite-key anyway.
-
没有-但是DBMS 禁止冗余是没有意义的，因为您需要增加逻辑和复杂性来处理这种情况，而没有真正的意义.两者都有带来的危害(除了必须保持两个索引的性能影响之外).
There is none - but it doesn't make sense for a DBMS to prohibit such redundancy either because you'd need added logic and complexity to handle that condition, whereas there's no real harm done by having both (besides the performance impact of having to maintain two indexes).
如上所述:因为检测和防止冗余的机会成本不值得.
As said above: because the opportunity cost of detecting and preventing that redundancy isn't worth it.
要考虑的另一件事是，表的主键定义不是不可变的，因此可能会发生变化.一个表可能已经具有设置了UNIQUE约束的列，然后数据库设计者决定将其包含在新的主键定义中-用户不友好，要求首先删除旧约束，尤其是如果其应用程序系统的其他部分依赖于那里的UNIQUE约束(例如，1:0..1关系定义).
Another thing to consider is that the primary-key definition of a table is not immutable and therefore is subject to change. A table might already have columns with a UNIQUE constraint set-up and then the database-designer decides to include that in a new definition of the primary-key - it would be a user-unfriendly to require the old constraint be removed first, especially if other parts of their application system depend on that UNIQUE constraint being there (e.g. a 1:0..1 relationship definition).
(此外，AUTO_INCREMENT与UNIQUE或PRIMARY KEY不互斥:您可以将AUTO_INCREMENT与非唯一列一起使用(例如，如果在表已经包含数据之后添加了AUTO_INCREMENT)，相反，PRIMARY KEY可以使用从其他位置获取的唯一值，例如另一个标识列作为外键(复合主键可以包含外键！)或自然"数据源，例如使用美国社会保险号作为一个主键(当然，您实际上应该从不执行此操作).
(Also, AUTO_INCREMENT is not mutually-inclusive with UNIQUE or PRIMARY KEY: you can use AUTO_INCREMENT with non-unique columns (e.g. if AUTO_INCREMENT is added after a table already contains data), and contrarywise a PRIMARY KEY can use unique values sourced from elsewhere, such as another identity column as a foreign key (composite primary keys can contain foreign keys!) or a "natural" data source, such as using a US Social Security Number as a Primary Key (of course you should never do this in reality)).
这篇关于MySQL PRIMARY KEY与UNIQUE约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
