MySql数据库·字段高级约束

一、高级约束分为：

主键 (primary key)

• 表头值不允许重复，且不允许赋NULL值
  
• 表中只能有一个主键表头
  
• 通常把表中唯一标识记录的表头设置为主键[记录编号表头]
  

自增长（auto_increment）

• 插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值
  
• 要想让表头有自增长 表头必须有主键设置才可以
  
• 自增长总是根据最后1条件记录表头的值+1结果赋值，除非通过truncate table这种方式清理表记录，才会从头开始计算。
  

复合主键

• 表中的多个表头一起做主键，称为复合主键
  
• 多条记录 主键的值不允许同时相同
  
• 复合主键必须同时一起创建，并同时删除
  

外键

• 外键字段的值必须在参考表字段值范围内
  
• 表存储（参照字段的表以及当前表）引擎必须要设置ENGINE=INNODB
  
• 设置外键的字段类型必须要与原表的参考字段要保持一致
  
• 被参照字段必须要是索引类型的一种(通常是 primary key)
  
• 未设置同步更新on update cascade，原表中被参考的字段不能被修改
  
• 未设置同步删除on delete cascade，原表中被参考的字段不能被删除
  

二、约束关键词：

关键词名称说明PRIMARY KEY设置字段为主键用于保证表头的值具有唯一性并且非空。
Key字段标志为：标志为PRIAUTO_INCREMENT设置字段自增长通常与主键字段连用
Extra字段标志为：auto_incrementFOREIGN KEY设置字段为外键保证数据的一致性，外键表头的值必须在参考表中表头值里选择
Key字段标志为：标志为MUL三、语法格式：

创建主键语法格式：

• 格式1：
  CREATE TABLE 库名.表名(
  字段名1 数据类型 PRIMARY KEY auto_increment
  字段名2 数据类型 约束条件1，约束条件2，约束条件3..
  ...
  )；
  
• 格式2：
  CREATE TABLE 库名.表名(
  字段名1 数据类型 约束条件1，约束条件2，约束条件3..
  ...,
  PRIMARY KEY(字段名1，字段2...)
  )；
  

删除主键语法格式：

ALTER TABLE 库名.表名 DROP PRIMARY KEY;
添加主键语法格式：

ALTER TABLE 库名.表名 ADD 字段名 数据类型 PRIMARY KEY auto_increment 位置命令;
创建外键语法格式：

CREATE TABLE 库名.表名(
字段名1 数据类型 约束条件1，约束条件2，约束条件3..
字段名2 数据类型 约束条件1，约束条件2，约束条件3..
...
FOREIGN KEY(字段名1)  # 指定外键
REFERENCES 库.表(表头名)  #指定参考的表头名
ON UPDATE CASCADE  #同步更新
ON DELETE CASCADE  #同步删除
) engine=innodb；
删除外键语法格式：

ALTER TABLE 库名.表名 DROP FOREIGN KEY 外键名(非字段名);

四、使用示例：

• 复合主键应用：
  

# 创建复合主键mysql> drop table  db1.t39;create table db1.t39(  cip varchar(15),   port smallint,    status enum("deny","allow"),   primary key(cip,port) # 同时设定多个主键);# 插入记录验证mysql> insert into  db1.t39  values ("1.1.1.1",22,"deny");# 插入二个主键的值同时相同会产生报错mysql> insert into  db1.t39  values ("1.1.1.1",22,"deny"); mysql> insert into  db1.t39  values ("1.1.1.1",80,"deny"); # 删除复合主键mysql> alter table db1.t39 drop primary key;# 查看表头mysql> desc db1.t39;+--------+----------------------+------+-----+---------+-------+| Field  | Type                 | Null | Key | Default | Extra |+--------+----------------------+------+-----+---------+-------+| cip    | varchar(15)          | NO   |     | NULL    |       || port   | smallint(6)          | NO   |     | NULL    |       || status | enum('deny','allow') | YES  |     | NULL    |       |+--------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

• 主键与auto_increment连用
  

mysql> create table db1.t39 (  行号 int auto_increment,   姓名 char(10),  班级 char(7),    住址 char(10),  primary key(行号));# 查看表头mysql> desc db1.t39;+--------+----------+------+-----+---------+----------------+| Field  | Type     | Null | Key | Default | Extra          |+--------+----------+------+-----+---------+----------------+| 行号   | int(11)  | NO   | PRI | NULL    | auto_increment || 姓名   | char(10) | YES  |     | NULL    |                || 班级   | char(7)  | YES  |     | NULL    |                || 住址   | char(10) | YES  |     | NULL    |                |+--------+----------+------+-----+---------+----------------+4 rows in set (0.01 sec)

• 清除表记录，从头开始计算
  

# truncate删除行,再添加行 从1开始mysql> truncate table db1.t39;  Query OK, 0 rows affected (2.66 sec)# 插入记录mysql> insert into db1.t39(  姓名,班级,住址)values(  "lucy","nsd2107","bj");Query OK, 1 row affected (0.04 sec)mysql> insert into db1.t39(  姓名,班级,住址)values(  "lucy","nsd2107","bj");Query OK, 1 row affected (0.30 sec)# 查看记录mysql> select * from db1.t39;+--------+--------+---------+--------+| 行号   | 姓名   | 班级    | 住址   |+--------+--------+---------+--------+|      1 | lucy   | nsd2107 | bj     ||      2 | lucy   | nsd2107 | bj     |+--------+--------+---------+--------+2 rows in set (0.01 sec)

• 给已有表添加行号字段（通常把表中唯一标识记录的字段作为主键 就是行号字段）
  

# 没加行号前查看mysql> select * from db1.t3;  +--------+---------+--------+| 姓名   | 班级    | 住址   |+--------+---------+--------+| qwer   | nsd2107 | bj     || rtyu   | nsd2107 | bj     |+--------+---------+--------+2 rows in set (0.01 sec)# 添加自增长表头idmysql> alter table db1.t3 add id int primary key auto_increment first; 添加后查看mysql> select * from db1.t3;  +--------+--------+---------+--------+| id     | 姓名   | 班级    | 住址   |+--------+--------+---------+--------+|      1 | qwer   | nsd2107 | bj     ||      2 | rtyu   | nsd2107 | bj     |+--------+--------+---------+--------+2 rows in set (0.01 sec)

• 创建带有外键的数据表
  

# 首先创建存储员工信息的员工表# 员工表  yg# 员工编号   姓名# yg_id     name  mysql> create table db1.yg(  yg_id int primary key auto_increment,   name char(16) ) engine=innodb;# 创建工资表 并指定外键表头# 工资表  gz# 员工编号  工资# gz_id     paymysql> create table db1.gz(  gz_id int,   pay  float,  foreign key(gz_id)   references db1.yg(yg_id)  on update cascade    on delete cascade)engine=innodb;# 查看工资表外键mysql> show create table db1.gz \G*************************** 1. row ***************************       Table: gzCreate Table: CREATE TABLE `gz` (  `gz_id` int(11) DEFAULT NULL,  `pay` float DEFAULT NULL,  KEY `gz_id` (`gz_id`),  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`)  # `gz_ibfk_1`是外键名  REFERENCES `yg` (`yg_id`)   ON DELETE CASCADE   ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1

• 删除外键
  

# 删除工资表的外键mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1;

• 给已存在的数据表中的字段添加外键
  

mysql> alter table db1.gz add   foreign key(gz_id)   references db1.yg(yg_id)    on update cascade    on delete cascade;# 查看外键mysql> show create table db1.gz \G*************************** 1. row ***************************       Table: gzCreate Table: CREATE TABLE `gz` (  `gz_id` int(11) DEFAULT NULL,  `pay` float DEFAULT NULL,  KEY `gz_id` (`gz_id`),  CONSTRAINT `gz_ibfk_1`   FOREIGN KEY (`gz_id`)   REFERENCES `yg` (`yg_id`)   ON DELETE CASCADE   ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)