主键SQL教程–如何在数据库中定义主键

Every great story starts with an identity crisis. Luke, the great Jedi Master, begins unsure - "Who am I?" - and how could I be anyone important? It takes Yoda, the one with the Force, to teach him how to harness his powers.

每个伟大的故事都始于身份危机。绝地大师大师卢克开始不确定- “我是谁？” -我怎么能成为重要人物？拥有部队的尤达需要教他如何利用自己的力量。

Today, let me be your Yoda.

今天，让我成为你的尤达。

We'll start with how to choose a Primary Key, fight an identity crisis, and then finish with code samples for creating a Primary Key in a database.

我们将从如何选择主键，应对身份危机开始，然后结束于在数据库中创建主键的代码示例。

如何选择主键 (How to Choose a Primary Key)

You may think Luke is the only one with an identity crisis, but that's not true. When creating a database, everything is in an identity crisis. And that's exactly why we need primary keys: they resolve the crisis. They tell us how to find everyone.

您可能会认为卢克是唯一遭受身份危机的人，但这不是事实。创建数据库时，一切都陷入身份危机。这就是我们需要主键的原因：它们可以解决危机。他们告诉我们如何找到每个人。

Imagine you're the government, and you want to identify each one of your citizens digitally. So, you create this database with everything about them:

假设您是政府，并且您希望以数字方式识别每个公民。因此，您将使用有关它们的所有内容来创建此数据库：

First Name
Last Name
Passport Number

You choose the passport Number as the Primary Key - the identity for everyone. You figure that's all you need since the passport has the address and everything else. You know passport numbers are unique, so you feel good and implement this system.

您选择护照号码作为主密钥-每个人的身份。您认为这就是您所需要的，因为护照上有地址和其他所有内容。您知道护照号码是唯一的，因此您会感觉良好并实施此系统。

Then, a few years later, you find out an ugly truth: the entire country is facing an identity crisis.

然后，几年后，您发现了一个丑陋的事实：整个国家都面临着身份危机。

Whenever someone's passport expires, they get a new one. Their identity changes. Other systems keep using the old passport numbers, so they now point to ghost people.

每当某人的护照过期时，他们都会换一张新护照。他们的身份改变了。其他系统继续使用旧的护照号码，因此现在它们指向虚假人员。

Uniqueness isn't enough. The value must not change throughout the row's lifetime.

唯一性还不够。该值在整个行的生存期内都不得更改。

And then, you find there are some people who don't even have passports. You can't enter them into your system, since Primary Keys can't be NULL. How can you identify someone with a NULL key?

然后，您会发现有些人甚至没有护照。您不能将它们输入到系统中，因为主键不能为NULL 。如何识别使用NULL键的人？

Every row must have an identifier. NULLs not allowed.

每行必须有一个标识符。不允许为NULL。

The next iteration means finding an identifier that doesn't change over time, and one that everyone has. In India, this is turning out to be the Adhaar Card. In the USA, the Social Security Number.

下一轮迭代意味着找到一个不会随时间变化的标识符，而每个人都会拥有。在印度，这真是Adhaar卡。在美国，社会安全号码。

If you're creating a database, make those your primary keys.

如果要创建数据库，请以这些为主键。

Sometimes, you don't have any such key. Consider a country that doesn't have a Social Security Number yet, and they want to create a digital record of every citizen. They could create a new SSN, or they could just leverage the power of databases, and use a surrogate key.

有时，您没有任何此类密钥。考虑一个尚无社会保险号的国家，他们想创建每个公民的数字记录。他们可以创建新的SSN，也可以仅利用数据库的功能并使用代理密钥。

A surrogate key has no real world equivalent. It's just a number inside a database. So, you have this table in the new country:

代理密钥没有现实世界的等效项。它只是数据库中的一个数字。因此，您在新国家/地区拥有此表格：

userID
First Name
Last Name
Passport Number

Passport Numbers are unique. Whenever you want to get the identifier for a user, you can get it via the Passport Number.

护照号码是唯一的。每当您想要获取用户的标识符时，都可以通过护照号码获取。

The userID never changes. The Passport Number can change - but it's always unique, so you always get the right user. The userID is a surrogate for a non-existing Social Security Number in this country.

userID永远不会更改。护照号码可以更改-但是它始终是唯一的，因此您始终可以找到合适的用户。 userID是该国家/地区不存在的社会保险号的替代。

Fun fact: The Passport Number here is also a Candidate Key. It could've been the Primary Key, if it never changed. This is a business logic distinction.

有趣的事实：这里的护照号码也是一个候选密钥。如果它从未更改，它可能是主键。这是业务逻辑上的区别。

The main takeaway is this: Whenever you're choosing a Primary Key, think of an identity crisis. Is it possible that someone might change their identifier in the future? Can we get into a state with multiple people having the same identifier?

主要收获是： 每当您选择主键时，请考虑一下身份危机 。将来有人可能会更改其标识符吗？我们可以进入多个人使用相同标识符的状态吗？

I use people as an example, because it makes identity clearer - we know every person is supposed to have an identity. Transfer this thinking to your databases. Everything has an identity, which is exactly why you need Primary Keys.

我以人为例，因为它使身份更清晰-我们知道每个人都应该有一个身份。将此思想转移到您的数据库。一切都有一个身份，这就是为什么您需要主键的原因。

Note: Sometimes, it's possible, and desirable to use multiple columns together as the Primary Key. This is a Composite Key.

注意：有时，并且有可能并希望同时使用多列作为主键。这是一个组合键。

Now let's try defining Primary Keys with real code examples. There's two things to do here: first, you'll identify the Primary Key. Then, you'll learn the syntax for defining it in a database.

现在，让我们尝试使用真实的代码示例来定义主键。这里有两件事要做：首先，您将识别主键。然后，您将学习在数据库中定义它的语法。

一个真实的例子 (A real world example)

Let's say you run a shipping startup, much like Flexport. You have packages that need to get from one place to another, and ships that transport them. Further, you have customers who are ordering these packages.

假设您经营一家货运公司，就像Flexport。您有需要从一个地方到达另一个地方的包裹，并需要将它们运输的船。此外，您有正在订购这些软件包的客户。

You figure you'll need one table for the customers, one for the packages, and one for transportation, showing which package is where right now.

您会发现您需要一张桌子供客户使用，一张需要包装，另外一张需要运输，以显示当前哪个包装。

Think through what columns you'll need, and what should be the Primary Key. If you were an engineer at Flexport, this is an actual question you would have to figure out. Nothing is given, everything is discovered in the real world.

考虑一下您将需要哪些列，什么应该是主键。如果您是Flexport的工程师，这是一个必须解决的实际问题。一无所有，一切都在现实世界中发现。

Given this information, I'd design these tables like so:

有了这些信息，我将像这样设计这些表：

Customers: first_name, last_name, email, address (for deliveries to their location)
Packages: weight, content
Transportation: <package_primary_key>, Port, time

We're missing the primary keys. Think about them before reading further.

我们缺少主键。在进一步阅读之前，请先考虑一下它们。

For the package, I'll choose a surrogate PackageID. I could have tried to list all the attributes of the package: weight, volume, density, age. They would uniquely identify the package, but this is very hard to do in practice. People don't care about this, they just care about the package getting from one place to another.

对于包，我将选择代理 PackageID。我本可以尝试列出包装的所有属性：重量，体积，密度，年龄。他们会唯一标识包装，但这在实践中很难做到。人们并不关心这一点，他们只是关心包裹从一个地方到另一个地方的运输。

So, it makes sense to create a random number and use that as the ID. This is exactly why you see FedEx, UPS, and every delivery service use barcodes and IDs. These are surrogate keys generated to track packages.

因此，创建一个随机数并将其用作ID很有意义。这就是为什么您看到FedEx，UPS和每个送货服务都使用条形码和ID的原因。这些是生成的用于跟踪包裹的代理密钥。

For the customer, I'll choose a surrogate CustomerID. Here, again, I had an option to choose, say, the Social Security Number of my customers. But, customers don't want to share this with me just so I can ship them something. Thus, we generate a key internally, don't tell our customers about this key, and continue calling them CustomerNo. 345681.

对于客户，我将选择一个替代客户ID 。同样，在这里，我可以选择客户的社会保险号。但是，客户不想仅与我分享此信息，以便我可以向他们发货。因此，我们在内部生成密钥，不要告诉客户此密钥，而是继续称其为CustomerNo。 345681。

Fun Story: I know a few companies where they exposed this CustomerNo, and the customers insisted they get No. 1. It was pretty hilarious - the engineers actually had to change their front-end code to: if (cust == 345681) print(1);
有趣的故事：我知道一些公司在这里公开此CustomerNo，并且客户坚持要获得No.1。这非常好笑-工程师实际上不得不将其前端代码更改为： if (cust == 345681) print(1);

For Transportation, I'll choose a composite PackageID+Port+time. This is a bit more interesting. I could have created a surrogate here as well, and it would work just as well.

对于运输，我将选择一个复合 PackageID + Port + time。这有点有趣。我也可以在这里创建一个代理，它也可以正常工作。

But, here lies the magic of indexing. The Primary Keys get an index automatically, which means searching is a lot more efficient over Primary Keys.

但是，这就是建立索引的魔力。主键会自动获取索引，这意味着搜索比主键要有效得多。

When you're searching through this database, most queries will be of the form "where is this package?". In other words, given this PackageID, tell me the Port and Time it is at right now. I would need an extra index over PackageID if I don't have it as part of my Primary Key.

当您搜索该数据库时，大多数查询将采用“此软件包在哪里？”的形式。换句话说，给定这个PackageID，告诉我现在的端口和时间。如果我没有将其作为主键的一部分，则需要在PackageID上有一个额外的索引。

Does this sound good? Final step, let's define these 3 tables in SQL. The syntax varies slightly with the database you're using.

听起来不错吗？最后一步，让我们在SQL中定义这3个表。语法随所使用的数据库而略有不同。

在MySQL中定义主键 (Defining Primary Keys in MySQL)

CREATE TABLE customers
( customerID  INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,last_name   VARCHAR(30) NOT NULL,first_name  VARCHAR(25) NOT NULL,email		  VARCHAR(50) NOT NULL,address     VARCHAR(300)
);

CREATE TABLE packages
( packageID  INT(15) NOT NULL AUTO_INCREMENT,weight     DECIMAL (10, 2) NOT NULL,content    VARCHAR(50),CONSTRAINT packages_pk PRIMARY KEY (packageID) # An alternative way to above,# when you want to name the constraint as well.
);

CREATE TABLE transportation
( package 	INT(15) NOT NULL,port  	INT(15) NOT NULL,time	 	DATE NOT NULL,PRIMARY KEY (package, port, time),FOREIGN KEY packageREFERENCES packages(packageID)ON DELETE RESTRICT    # It's good practice to define what should happen on deletion. In this case, I don't want things to get deleted.);

在PostgreSQL中定义主键 (Defining Primary Keys in PostgreSQL)

CREATE TABLE customers
( customerID  SERIAL NOT NULL PRIMARY KEY, # In PostgreSQL SERIAL is same as AUTO_INCREMENT - it adds 1 to every new row.last_name   VARCHAR(30) NOT NULL,first_name  VARCHAR(25) NOT NULL,address     TEXT,email		  VARCHAR(50) NOT NULL
);

CREATE TABLE packages
( packageID  SERIAL NOT NULL,weight     NUMERIC NOT NULL,content    TEXT,CONSTRAINT packages_pk PRIMARY KEY (packageID) # In PostgreSQL, this alternative way works too.
);

CREATE TABLE transportation
( package 	INTEGER NOT NULL,port  	INT(15) NOT NULL,time	 	DATE NOT NULL,PRIMARY KEY (package, port, time),FOREIGN KEY packageREFERENCES packages(packageID)ON DELETE RESTRICT    # It's good practice to define what should happen on deletion. In this case, I don't want things to get deleted.);

It's not very different, is it? Once you get the basics down, you can apply it to almost any database with just a quick look at the documentation. The key is knowing what to look for!

不是很不同，是吗？掌握基础知识之后，只需快速浏览一下文档，便可以将其应用于几乎所有数据库。关键是知道要寻找什么！

Good luck, young padawan.

祝您好运，年轻的帕达万。

Enjoyed this? You might also like Things I Learned From a Senior Software Engineer

喜欢这个吗？您可能还喜欢我从高级软件工程师那里学到的东西