MySQL初步易懂之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()用法

网站地图xml地图