Tables, keys and relationships · 表、键与关系
More than one table
Real databases split data across several tables. This lesson uses a small shop with two of them:
customer— one row per customer (id,name,city)orders— one row per order (id,customer_id,order_date,total)
Storing the customer's name on every order would repeat it again and again. Keeping customers in their own table stores each one once.
不止一张表
真实的数据库把数据分散在多张表里。本课用一个小商店,它有两张表:
customer—— 每位顾客一行(id、name、city)orders—— 每张订单一行(id、customer_id、order_date、total)
如果把顾客的名字存在每一张订单上,就会一遍遍地重复。把顾客单独放一张表,每位顾客只存一次。
Primary keys
A primary key is the column that uniquely identifies each row. It is never blank and never repeated.
customer.idis the primary key ofcustomer.orders.idis the primary key oforders.
Given a primary key value, you can always find exactly one row.
主键
主键是唯一标识每一行的那一列。它从不为空,也从不重复。
customer.id是customer的主键。orders.id是orders的主键。
给定一个主键值,你总能找到恰好一行。
Foreign keys
A foreign key is a column that holds the primary key of another table, linking the two.
orders.customer_id holds a value from customer.id — it says which customer each order belongs to.
Referential integrity means every foreign-key value must match a real primary key: an order can't belong to a customer who doesn't exist.
外键
外键是一列,它保存另一张表的主键,把两张表连接起来。
orders.customer_id 保存的是来自 customer.id 的值 —— 它说明每张订单属于哪位顾客。
参照完整性指每个外键值都必须对应一个真实存在的主键:订单不能属于一个不存在的顾客。
Common mistakes
- A PRIMARY KEY is unique and cannot be NULL.
- A foreign key points to another table's primary key.
常见错误
- 主键唯一且不能为 NULL。
- 外键指向另一张表的主键。
Keys link two tables · 键把两个表连起来
A shared key · 键 lets a row in one table point to a row in another. · 共享的键让一个表的行指向另一个表的行。
Show the whole customer table so you can see the rows and their primary key id. · 显示整张 customer 表,看看里面的行和它们的主键 id。
Click Run to see the output here. · 点击“运行”查看此处输出。
Show each order's id, its customer_id (the foreign key), and its total, ordered by id. · 显示每张订单的 id、它的 customer_id(外键)和 total,按 id 排序。
Click Run to see the output here. · 点击“运行”查看此处输出。