mysql联合主键sql mysql联合主键约束

1. 主键约束

主键约束要求列的数据唯一，并且不能为空。
主键能够唯一地标识表中的一条记录。
主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。

1.1 单字段主键
直接在定义列的时候指定主键即可。

create table temp1(
num int PRIMARY KEY,
name varchar(11)
);

1.2 多字段主键

主键由多个字段联合组成，规则如下：
PRIMARY KEY(字段1，字段2...)

---

// 多个字段联合创建主键时, 不能按照以下方式创建, 否则报错
drop table If EXISTS temp1;
create table temp1(
num int PRIMARY KEY,
id int PRIMARY KEY,
name varchar(11)
);

---

// 正确方式, 创建成功
drop table If EXISTS temp1;
create table temp1(
num int,
id int,
name varchar(11),
PRIMARY KEY(num, id)
);

2. 外键约束

外键用来在两个表的数据之间建立链接，可以是一列或多列。
一个表可以有一个或多个外键。
2.0 数据源

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` int(11) PRIMARY KEY COMMENT '部门id',
  `departName` varchar(255) COMMENT '部门名字'
);
-- ---------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (111, '研发部');
INSERT INTO `department` VALUES (222, '测试部');
INSERT INTO `department` VALUES (333, '行政部');
INSERT INTO `department` VALUES (444, '后勤部');
INSERT INTO `department` VALUES (555, '仓库');

-- ----------------------------
-- Table structure for nation
-- ----------------------------
DROP TABLE IF EXISTS `nation`;
CREATE TABLE `nation`  (
  `id` int(11) PRIMARY KEY COMMENT '民族id',
  `nation` varchar(255) COMMENT '民族'
);
-- ----------------------------
-- Records of nation
-- ----------------------------
INSERT INTO `nation` VALUES (103, '回族');
INSERT INTO `nation` VALUES (101, '汉族');
INSERT INTO `nation` VALUES (104, '蒙古族');
INSERT INTO `nation` VALUES (102, '高山族');

2.1 在创建表的时候指定创建外键
格式：CONSTRAINT 外键名 FOREIGN KEY(从表字段) REFERENCES 主表名(主表字段)

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `num` int(11) PRIMARY KEY COMMENT '学号',
  `name` varchar(10) COMMENT '姓名',
  `department` int(11),
  `nation` int(11),
  CONSTRAINT `f_department` FOREIGN KEY (`department`) REFERENCES `department` (`id`),
  CONSTRAINT `f_nation` FOREIGN KEY (`nation`) REFERENCES `nation` (`id`)
);

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, 'zhang', 111, 101);
INSERT INTO `student` VALUES (102, 'wang', 111, 102);
INSERT INTO `student` VALUES (103, 'li', 222, 103);
INSERT INTO `student` VALUES (104, 'zhao', 111, 103);
INSERT INTO `student` VALUES (105, 'han', 222, 102);
INSERT INTO `student` VALUES (106, 'cheng', 222, 104);
INSERT INTO `student` VALUES (107, 'long', 333, 104);

如上所示，我们在studnet表中已经创建了两个外键，其分别关联了department表和nation表中的主键字段。

我们称student表为从表(子表)，而department表和nation表为主表(父表)。

从上可以清晰的看出，此时student表中的department列和nation列中的数据只能是来自主表中关联字段的数据，不能是其他数据，否则无法插入成功。如下所示，企图修改为5555，但是5555不是主表department中主键的数据，直接报错。

2.2 单独为表添加外键
格式：ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(从表字段) REFERENCES 主表名(主表字段)

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `num` int(11) PRIMARY KEY COMMENT '学号',
  `name` varchar(10) COMMENT '姓名',
  `department` int(11),
  `nation` int(11)
);

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, 'zhang', 111, 101);
INSERT INTO `student` VALUES (102, 'wang', 111, 102);
INSERT INTO `student` VALUES (103, 'li', 222, 103);
INSERT INTO `student` VALUES (104, 'zhao', 111, 103);
INSERT INTO `student` VALUES (105, 'han', 222, 102);
INSERT INTO `student` VALUES (106, 'cheng', 222, 104);
INSERT INTO `student` VALUES (107, 'long', 333, 104);

// 接下来, 为student创建外键, 将其与department表和nation表关联起来
ALTER TABLE student ADD CONSTRAINT `f_department` FOREIGN KEY (`department`) REFERENCES `department` (`id`);
ALTER TABLE student ADD CONSTRAINT `f_nation` FOREIGN KEY (`nation`) REFERENCES `nation` (`id`);

2.3 删除外键

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。
格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

alter table student drop FOREIGN KEY f_department; 
alter table student drop FOREIGN KEY f_nation;

2.4 外键的删除/更新行为
NO ACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
CASCADE：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL：在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null。（这就要求该外键值允许取null）
SET DEFAULT：父表有变更时，子表将外键列设置成一个默认的值。(InnoDB不支持)

格式：ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(从表字段) REFERENCES 主表名(主表字段) ON UPDATE 行为 ON DELETE 行为

具体演示此处不再赘述，按照之前的数据自行更改测试即可。

3. 非空约束

格式：字段名 数据类型 not null
非空约束指代字段的值不能为空。对于使用了非空约束的字段，如果添加数据时没有指定值，那么直接报错。

4. 唯一性约束

格式：字段名 数据类型 UNIQUE
或者 CONSTRAINT 约束名 UNIQUE(字段)
唯一性约束要求该列唯一，允许为空，但只能出现一个空值。
唯一性约束可以保证一列或者几列的值不出现重复值。

DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
  `num` int(11) UNIQUE,
  `name` varchar(255) 
) ;

DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
  `num` int(11),
  `name` varchar(255),
	CONSTRAINT u_num UNIQUE(num)
) ;

5. 默认约束

格式：字段名 数据类型 DEFAULT 默认值
默认约束用来指定某列的默认值。如果插入记录时没有为该字段赋值，那么系统会自动为这个字段赋值为指定的默认值。

---

设置表的属性自动增加：字段 数据类型 auto_increment

mysql> create table student (
    -> num int  comment "学号" PRIMARY KEY auto_increment,
    -> name varchar(10)  default "zhang" comment "姓名" not null
    -> );
Query OK, 0 rows affected (0.27 sec)

mysql> desc student
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | zhang   |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)