SQL语句第二节

约束

什么是约束？

约束是确保表当中的数据的完整性（正确性和正确性），对表添加时进行了一些限制。使数据更加准确，减少冗余数据。

数据库的完整性约束

主键约束：每一个数据都要有一个唯一标识符

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

INSERT INTO student(name,age,email) VALUES ('张安',18,'1443005893@qq.com')
INSERT INTO student(name,age,email) VALUES ('李四',20,'1443005893@qq.com')

自动递增 AUTO_INCREMENT 的特点

1.只有整形数据列才能设置为自动递增 2.只有主键才能设置为自增列 3.自动增长，新增数据时可以不赋值 4.初始化默认值为1，增量为1 5.自增列一旦被使用过就不会出现

唯一约束：用于保护表中不允许出现重复数据的列

创建索引，长度先改小

非空约束：某些字段不允许为空

默认约束：某些字段需要一些默认值

外键约束：有一定必然性联系

CREATE TABLE `husband`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for wife
-- ----------------------------
DROP TABLE IF EXISTS `wife`;
CREATE TABLE `wife`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `husband` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;