1. 很大的表,经常被用于查询的表,有主键或者Unique index是很重要的,这种索引的速度最快。 2. 一张表可以有多个Unique index,但必须保证建Unique index的字段,必须保证该字段的值是唯一的。 3. 主键简单地说,就是不能为空的Unique index
比如: CREATE TABLE `NewTable` ( `id` int(8) NOT NULL AUTO_INCREMENT , `school_id` int(8) NOT NULL , `school_name1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `school_name2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `delete_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `delete_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `delete_flag` int(8) NULL DEFAULT NULL , PRIMARY KEY (`id`), UNIQUE INDEX `school_id` USING BTREE (`school_id`), UNIQUE INDEX `school_name1` USING BTREE (`school_name1`), UNIQUE INDEX `school_name2` USING BTREE (`school_name2`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=11 ROW_FORMAT=COMPACT ;
CREATE TABLE `NewTable` ( `id` int(8) NOT NULL AUTO_INCREMENT , `class_id` int(8) NOT NULL , `class_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `class_schoolid` int(8) NOT NULL , `class_specialityid` int(8) NOT NULL , `delete_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `delete_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `delete_flag` int(8) NULL DEFAULT NULL , PRIMARY KEY (`id`), FOREIGN KEY (`class_schoolid`) REFERENCES `school` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, FOREIGN KEY (`class_specialityid`) REFERENCES `speciality` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, UNIQUE INDEX `class_id` USING BTREE (`class_id`), UNIQUE INDEX `class_name` USING BTREE (`class_name`), INDEX `FK_cclass` USING BTREE (`class_schoolid`), INDEX `FK_cclass_special` USING BTREE (`class_specialityid`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci COMMENT='InnoDB free: 9216 kB; (`class_schoolid`) REFER `g1/school`(`id`); (`class_specia' AUTO_INCREMENT=2 ROW_FORMAT=COMPACT ;
而PK就不能为空了.
不过这在一定程度上也反应出你们的系统设计方面可能存在一些问题.
2. 一张表可以有多个Unique index,但必须保证建Unique index的字段,必须保证该字段的值是唯一的。
3. 主键简单地说,就是不能为空的Unique index
CREATE TABLE `NewTable` (
`id` int(8) NOT NULL AUTO_INCREMENT ,
`school_id` int(8) NOT NULL ,
`school_name1` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`school_name2` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`delete_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`delete_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`delete_flag` int(8) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE INDEX `school_id` USING BTREE (`school_id`),
UNIQUE INDEX `school_name1` USING BTREE (`school_name1`),
UNIQUE INDEX `school_name2` USING BTREE (`school_name2`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=11
ROW_FORMAT=COMPACT
;
`id` int(8) NOT NULL AUTO_INCREMENT ,
`class_id` int(8) NOT NULL ,
`class_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`class_schoolid` int(8) NOT NULL ,
`class_specialityid` int(8) NOT NULL ,
`delete_date` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`delete_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`delete_flag` int(8) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`class_schoolid`) REFERENCES `school` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`class_specialityid`) REFERENCES `speciality` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
UNIQUE INDEX `class_id` USING BTREE (`class_id`),
UNIQUE INDEX `class_name` USING BTREE (`class_name`),
INDEX `FK_cclass` USING BTREE (`class_schoolid`),
INDEX `FK_cclass_special` USING BTREE (`class_specialityid`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='InnoDB free: 9216 kB; (`class_schoolid`) REFER `g1/school`(`id`); (`class_specia'
AUTO_INCREMENT=2
ROW_FORMAT=COMPACT
;