通俗易懂之MySQL外键、主键的使用

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. --引用官方

简单说，外键约束就是把两张表的数据通过某种条件关联起来, 作用是保持数据完整性和一致性

语法如下

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Adding foreign keys

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

Dropping Foreign Keys

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

例子，
有一个parent表,含一个字段id(int)
有一个child表，含两个字段分别是 id(int) 和 parent_id(int)
现在为child表增加删除联动(delete cascade)外键，当 parent 表 的 id=2 的记录被删除时，如果对应 child 表有相对应的parent_id=2 ,那么 child 表的该行记录也会被删除。

//创建表parent，并插入数据
CREATE TABLE `parent` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into parent values(1),(2),(3);

// 创建表child,并插入数据
CREATE TABLE `child` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into child values(10,1),(20,2),(30,3);  

// 为child表添加一个外键，与parent表的关系是删除联动
// 当parent表的记录被删除时，如果指定的外键parent_id=id时，child表的相对应的记录也同时被删除
ALTER TABLE child ADD CONSTRAINT child_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;

//删除parent表id=2的记录
delete from parent where id=2;

// 发现child表parent_id=2的记录也被删除掉了
select * from child;

详情请看Using FOREIGN KEY Constraints

顺带说说主键(Primary key)

A primary key is a column or a set of columns that uniquely identifies each row in the table. You must follow the rules below when you define a primary key for a table:

• A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
• A primary key column cannot contain NULL values. It means that you have to declare the primary key column with the NOT NULL attribute. If you don’t, MySQL will force the primary key column as NOT NULL implicitly.
• A table has only one primary key.

简单而言，主键就是一个或多个字段，用来唯一标识表中的每行记录
主键的值必须是唯一的，非空的，每个表只有一个主键
主键的数据类型，最好是 int 类型，Because MySQL works faster with integers
主键通常设为 auto_increment，自动生成唯一的序列值

添加主键

ALTER TABLE table_name ADD PRIMARY KEY(primary_key_column);

Primary key vs. unique key vs. key

• key 的同义词就是index索引，当你想为字段创建索引create an index的时候，用到key关键词
• unique key 与Primary key相似，要求值是唯一的，不同的是：允许为NULL值，mysql也允许可以设定多个unique indexs

Introduction to MySQL primary key
MySQL Managing Database Index

额外提一下好用的replace()

简单而言，就是替换掉你想更改的东西
应用场景，表t1里的url字段里的所有记录中，要把http改成https

UPDATE t1 SET url=REPLACE(url,'http','https');

详情请看replace()用法