约束和索引
约束信息保存在information_schema.table_constraints表中
select * from information_schema.table_constraints where table_schema = "pmx"; Empty set (0.00 sec)
索引信息保存在information_schema.statistics表中
select * from information_schema.statistics where table_schema = 'pmx'; Empty set (0.00 sec)
mysql的约束分为主键约束，外键约束和唯一性约束。
创建具有主键的表t1
Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
在创建约束时，mysql会自动创建该字段的索引
select * from information_schema.table_constraints where table_schema = "pmx"; +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | def | pmx | PRIMARY | pmx | t1 | PRIMARY KEY | +--------------------+-------------------+-----------------+--------------+------------+-----------------+
select * from information_schema.statistics where table_schema = 'pmx'\G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT:
为t1添加唯一性约束
alter table t1 add column a int; alter table t1 add constraint a_unique unique key(a);
给唯一约束指定自定义名称时，需要分两行写
alter table t1 add column a int unique key;
不需要指定约束名称可以写成一行
查看约束和索引
select * from information_schema.table_constraints where table_schema = "pmx"; +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | def | pmx | PRIMARY | pmx | t1 | PRIMARY KEY | | def | pmx | a | pmx | t1 | UNIQUE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+
select * from information_schema.statistics where table_schema = 'pmx'\G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: a SEQ_IN_INDEX: 1 COLUMN_NAME: a COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT:
为t1添加外键约束
alter table t1 add column b int; alter table t1 add constraint c_fk foreign key (b) references t2(id);
表结构更新为：
Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a_unique` (`a`), KEY `c_fk` (`b`), CONSTRAINT `c_fk` FOREIGN KEY (`b`) REFERENCES `t2` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
t1的约束列表
+--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | def | pmx | PRIMARY | pmx | t1 | PRIMARY KEY | | def | pmx | a_unique | pmx | t1 | UNIQUE | | def | pmx | c_fk | pmx | t1 | FOREIGN KEY | | def | pmx | PRIMARY | pmx | t2 | PRIMARY KEY | +--------------------+-------------------+-----------------+--------------+------------+-----------------+
但是为t1唯一索引会创建唯一性约束
alter table t1 add unique index(c);
select * from information_schema.table_constraints where table_schema = "pmx"; +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | def | pmx | PRIMARY | pmx | t1 | PRIMARY KEY | | def | pmx | a_unique | pmx | t1 | UNIQUE | | def | pmx | c | pmx | t1 | UNIQUE | | def | pmx | c_fk | pmx | t1 | FOREIGN KEY | | def | pmx | PRIMARY | pmx | t2 | PRIMARY KEY | +--------------------+-------------------+-----------------+--------------+------------+-----------------+
select * from information_schema.statistics where table_schema = 'pmx'\G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: a_unique SEQ_IN_INDEX: 1 COLUMN_NAME: a COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 3. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: c SEQ_IN_INDEX: 1 COLUMN_NAME: c COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 4. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 1 INDEX_SCHEMA: pmx INDEX_NAME: c_fk SEQ_IN_INDEX: 1 COLUMN_NAME: b COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 5. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t2 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT:
为t1添加其他的普通索引,全文索引则不会创建约束
alter table t1 add column d int,add column e char(20)
create index d_index on t1 (d);
alter table t1 add fulltext index e_index (e);
结果
select * from information_schema.table_constraints where table_schema = "pmx"; +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | def | pmx | PRIMARY | pmx | t1 | PRIMARY KEY | | def | pmx | a_unique | pmx | t1 | UNIQUE | | def | pmx | c | pmx | t1 | UNIQUE | | def | pmx | c_fk | pmx | t1 | FOREIGN KEY | | def | pmx | PRIMARY | pmx | t2 | PRIMARY KEY | +--------------------+-------------------+-----------------+--------------+------------+-----------------+
select * from information_schema.statistics where table_schema = 'pmx'\G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: a_unique SEQ_IN_INDEX: 1 COLUMN_NAME: a COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 3. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: c SEQ_IN_INDEX: 1 COLUMN_NAME: c COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 4. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 1 INDEX_SCHEMA: pmx INDEX_NAME: c_fk SEQ_IN_INDEX: 1 COLUMN_NAME: b COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 5. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 1 INDEX_SCHEMA: pmx INDEX_NAME: d_index SEQ_IN_INDEX: 1 COLUMN_NAME: d COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 6. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t1 NON_UNIQUE: 1 INDEX_SCHEMA: pmx INDEX_NAME: e_index SEQ_IN_INDEX: 1 COLUMN_NAME: e COLLATION: NULL CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: FULLTEXT COMMENT: INDEX_COMMENT: *************************** 7. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: pmx TABLE_NAME: t2 NON_UNIQUE: 0 INDEX_SCHEMA: pmx INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT:
可以看到索引表中有d和e字段，而在约束表中没有
