mysql 外键_MySQL 基本语句十 Primary Key & Foriegn Key（更新6/10/2020）

SQL PRIMARY KEY Constraint

SQL 主键约束

主键包含的都是Unique 不重复，Not NULL 不为空的数据。

The PRIMARY KEY constraint uniquely identifies each record in a table.

一个表格只能有一个主键，逐渐可以包含一个或者多个列。

SQL PRIMARY KEY on CREATE TABLE

下面是一个在persons表格里面，设定ID为主键的代码：

MySQL:

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

下面是一个在Persons 表格里面设定ID和Last_name为主键的列的代码：

MySQL:

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

SQL PRIMARY KEY on ALTER TABLE

对于已经建立好的表格，

设定单列主键:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

设定多列主键:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

一定要确定所设定为主键的列没有空值。

DROP a PRIMARY KEY Constraint

去掉主键:

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

---

SQL FOREIGN KEY Constraint

外键的作用：使两个表相互连接。

一个表里的外键指向另外一个表中的主键。

含有外键的表叫做子表。

下面有两个表:

"Persons" table:

PersonID LastName FirstName Age

1 Hansen Ola 30

2 Svendson Tove 23

3 Pettersen Kari 20

"Orders" table:

OrderID OrderNumber PersonID

1 77895 3

2 44678 3

1 77895 32

4 24562 1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

在Orders这个表里的PersonID用来指向Persons表里的PersonID这一列。

在Persons这个表里，PersonID是主键。

在Orders这个表里，PersonID是外键。

外键可以防止操作破坏两个表之间的联系，也可以组织数据添加（添加的数据必须存在于外键所向的数据表的那一列里）

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

建立PersonID为外键，指向Persons表格的PersonID

MySQL:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

在多个列上建立外键：

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

在已存在表格里建立外键:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

去掉外键:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

---

概念再总结：

这个网页总结到了key/composite key/primary key/foreign key

What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples​www.guru99.com

几个讲的不错的点：

What is a KEY?
A KEY is a value used to identify a record in a table uniquely. A KEY could be a single column or combination of multiple columns
Note: Columns in a table that are NOT used to identify a record uniquely are called non-key columns.What is a Primary Key?

A primary is a single column value used to identify a database record uniquely.

It has following attributes

• A primary key cannot be NULL
• A primary key value must be unique
• The primary key values should rarely be changed
• The primary key must be given a value when a new record is inserted.

What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a record uniquely

In our database, we have two people with the same name Robert Phil, but they live in different places.

Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.

Let's move into second normal form 2NF

2NF (Second Normal Form) Rules

• Rule 1- Be in 1NF
• Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2nd Normalization form unless we partition the table above.

Table 1

Table 2

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains member information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id

Database - Foreign Key

In Table 2, Membership_ID is the Foreign Key

Foreign Key references the primary key of another Table! It helps connect your Tables

• A foreign key can have a different name from its primary key
• It ensures rows in one table have corresponding rows in another
• Unlike the Primary key, they do not have to be unique. Most often they aren't
• Foreign keys can be null even though primary keys can not

Why do you need a foreign key?

Suppose, a novice inserts a record in Table B such as

You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity.

The above problem can be overcome by declaring membership id from Table2 as foreign key of membership id from Table1

Now, if somebody tries to insert a value in the membership id field that does not exist in the parent table, an error will be shown!

reference： What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples