Tables, keys and relationships
Databases & SQL Lesson 9 2:07 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Real databases hold several tables, and the reason is easiest to see by doing it wrong first.
真实的数据库会有好几张表，而这样做的理由，先做错一次最容易看清楚。
Put everything in one table and Ada's name and city are written out twice — once per order.
把所有东西都放进一张表，Ada 的名字和城市就被写了两遍——每一单一遍。
That is not just wasted space.
这不只是浪费空间。
If she moves house, two rows must change together, and if you miss one the database now says two different things about the same person.
如果她搬家了，两行必须一起改， 而只要漏掉一行，这个数据库现在就对同一个人说了两种不同的话。
The fix is to give each kind of thing its own table.
解决办法是给每一"类"东西自己的一张表。
Customers here, one row each.
客户在这边，一人一行。
Orders there, one row each.
订单在那边，一单一行。
Now Ada's city is written down exactly once, no matter how many orders she places, and a change to it is a change to one row.
现在 Ada 的城市只被写下一次，不管她下了多少单， 而修改它就是修改一行。
Each table needs a way to name one row and only one, and that is the primary key.
每张表都需要一种方式来指出某一行、而且只有那一行，那就是主键。
Two rules define it.
两条规则定义了它。
It is unique — no two rows share it.
它是唯一的——没有两行共用同一个值。
And it is never blank, never NULL.
而且它从不为空，从不是 NULL。
Together those mean that given a primary key value, you can always find exactly one row, which is what makes the link in a moment possible at all.
这两条合起来意味着：给你一个主键值，你总能找到恰好一行， 而这正是接下来那个"链接"能够成立的全部前提。
And a foreign key is the link itself: a column that holds another table's primary key.
而外键就是那条链接本身：一列，装着另一张表的主键。
Every order carries a customer_id, which says which customer it belongs to.
每一个订单都带着一个 customer_id，说明它属于哪个客户。
Follow the arrow and you land on exactly one customer row.
顺着箭头走，你会落在恰好一行客户上。
One rule protects this — referential integrity — which says every foreign key must match a real primary key.
有一条规则在保护这件事——参照完整性—— 它规定每一个外键都必须对应一个真实存在的主键。
An order cannot belong to a customer who does not exist.
一个订单不能属于一个不存在的客户。
Four things to take with you.
带走四点。
One: repeating the same fact in many rows is the problem all of this solves.
第一：把同一个事实在很多行里重复，才是这一切要解决的问题。
Two: a primary key is unique and never NULL.
第二：主键是唯一的，而且从不为 NULL。
Three: a foreign key holds another table's primary key.
第三：外键装的是另一张表的主键。
Four: referential integrity means every link must point at a real row.
第四：参照完整性意味着每一条链接都必须指向一个真实存在的行。
Now run the tasks below.
现在去做下面的题。