当我已经有了索引时，如何以及为什么将主键添加到我的 SQL 数据库表中 [英] How and why to add primary keys to my SQL database table when I already have an index

问题描述

我有一个名为 datatable 的 sql 表，

I have the following sql table called datatable,

date       yesteday today tomorrow reading source
2021-01-01 x        x     x        x       1
2021-01-01 x        x     x        x       2
2021-01-01 x        x     x        x       3
...
2021-01-02 x        x     x        x       1
2021-01-02 x        x     x        x       2
2021-01-02 x        x     x        x       3
...
2021-05-31 x        x     x        x       1
2021-05-31 x        x     x        x       2
2021-05-31 x        x     x        x       3

当我创建表时，我设置了以下内容，

When I created the table, I set the following,

create table datatable 
(
    date date, 
    yesterday real, 
    today real, 
    tomorrow real, 
    reading real, 
    source varchar
)

然后我创建了一个索引:

Then I created an index:

create index datatable_idx on datatable (date, source)

每天，源编号都会重复.所以日期和来源将是唯一的.

Every day, the source numbers will repeat themselves. So date together with source would be unique.

我想改进我的数据收集和存储过程.有人告诉我应该创建一个主键.

I want to improve my data collection and storing process. I was told that I should create a primary key.

我猜这里使用的命令是

ALTER TABLE datatable ADD PRIMARY KEY (date, source)

我的问题是我为什么要这样做，这与我创建的索引有什么区别.会影响我的流程吗?

My question is why should I do this and what is the difference between this and the index I created. Will it affect my process?

另外，下次我创建表时，我是否必须像这样创建索引和主索引，或者有没有办法在创建时同时创建?

Also next time I create a table, do I have to create an index and primary like this or is there a way to do both upon creation?

推荐答案

主键是一个约束，它指定其列中的值必须(相互)唯一且不为空.这样可以保证唯一标识表中的每一行，这在想要创建引用此表的外键时最有用.如果您没有其他表，它仍然值得拥有，因为它可以防止您的表进入不良状态，例如，您在特定日期拥有多个相同的源值.

A primary key is a constraint that specifies that the values in its column(s) must be (mutually) unique and not null. This is so that it can be guaranteed to uniquely identify every row in the table, which is most useful when wanting to create foreign keys that refer to this table. It's still worth having if you don't have other tables, as it will prevent your table getting into a bad state where, for example, you have more than one of the same source value on a particular date.

主键字段几乎总是有一个索引，它们经常用于查找和联接，但这两个概念是分开的.

The primary key field(s) will almost always have an index on them as well as they are used often for lookups and JOINs, but the two concepts are separate.

一些 DBMS(例如 MySQL、SQL Server)会自动在主键上创建聚集索引，这意味着表中的数据在磁盘上按组成主键的字段进行排序，以使上述常见操作更快.但是，默认情况下 postgres 不会这样做.

Some DBMSs (e.g. MySQL, SQL Server) automatically create a clustered index on the primary key, meaning that data in the table is sorted on disk by the field(s) comprising the primary key to make the common operations above even faster. However, postgres does not do this by default.

您可以使用以下语法在创建表时指定主键:

You can specify the primary key on table creation using the following syntax:

create table datatable 
(
    date date, 
    yesterday real, 
    today real, 
    tomorrow real, 
    reading real, 
    source varchar,
    PRIMARY KEY (source, date)
)

您还可以添加一个新的、自动递增的整数字段作为您的主键(通常称为代理键或人工键).如果您的数据项中没有任何其他合适的候选对象，您可能想要这样做，但它也有其他好处(例如可能更快的 JOIN).

You can also add a new, auto-incrementing integer field to act as your primary key (generally referred to as a surrogate key or artificial key). You might want to do this if you don't have any other good candidates in your data items, but it can have other benefits as well (potentially faster JOINs for example).

create table datatable 
(
    id serial primary key,
    date date, 
    yesterday real, 
    today real, 
    tomorrow real, 
    reading real, 
    source varchar
)

这篇关于当我已经有了索引时，如何以及为什么将主键添加到我的 SQL 数据库表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！