主键还是唯一索引? [英] Primary key or Unique index?
问题描述
在工作中,我们有一个拥有唯一索引而不是主键的大数据库,所有工作都很好。
我为一个新项目设计新数据库,一个困境:
在DB理论中,主键是基本元素,这是可以的,但在REAL项目中有什么优点和缺点?
在项目中使用什么?
EDIT: ...以及主键和复制MS SQL Server?
什么是唯一索引?
列上的唯一索引是该列上的索引,该索引还强制实施约束,在两列不同的列中不能有两个相等的值。示例:
CREATE TABLE table1(foo int,bar int);
CREATE UNIQUE INDEX ux_table1_foo ON table1(foo); - 在foo上创建唯一索引。
INSERT INTO table1(foo,bar)VALUES(1,2); - OK
INSERT INTO table1(foo,bar)VALUES(2,2); - OK
INSERT INTO table1(foo,bar)VALUES(3,1); - OK
INSERT INTO table1(foo,bar)VALUES(1,4); - 失败!
键'ux_table1_foo'的重复条目'1'
最后一个插入失败,因为它违反了唯一索引
在MySQL a中,在列 foo
唯一约束允许多个NULL。
可以在多个列上创建唯一索引。
主键与唯一索引
相同的内容:
- 主键意味着唯一索引。
不同的东西:
- 主键也意味着NOT NULL,但唯一索引可以为空。
- 只能有一个主键,多个唯一索引。
- 如果没有定义聚集索引,则主键将是聚集索引。
At work we have a big database with unique indexes instead of primary keys and all works fine.
I'm designing new database for a new project and I have a dilemma:
In DB theory, primary key is fundamental element, that's OK, but in REAL projects what are advantages and disadvantages of both?
What do you use in projects?
EDIT: ...and what about primary keys and replication on MS SQL server?
What is a unique index?
A unique index on a column is an index on that column that also enforces the constraint that you cannot have two equal values in that column in two different rows. Example:
CREATE TABLE table1 (foo int, bar int); CREATE UNIQUE INDEX ux_table1_foo ON table1(foo); -- Create unique index on foo. INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails! Duplicate entry '1' for key 'ux_table1_foo'
The last insert fails because it violates the unique index on column foo
when it tries to insert the value 1 into this column for a second time.
In MySQL a unique constraint allows multiple NULLs.
It is possible to make a unique index on mutiple columns.
Primary key versus unique index
Things that are the same:
- A primary key implies a unique index.
Things that are different:
- A primary key also implies NOT NULL, but a unique index can be nullable.
- There can be only one primary key, but there can be multiple unique indexes.
- If there is no clustered index defined then the primary key will be the clustered index.
这篇关于主键还是唯一索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!