MySQL、SQLServer、Oracle中约束和索引的关系

约束类型 MySQL SQL Server Oracle 主键约束 生成唯一索引 生成唯一索引 使用存在的索引或者生成唯一索引 外键约束 生成索引 不生成索引 不生成索引 唯一约束 生成唯一索引 生成唯一索引 使用已存在的索引或者创建新索引

举例：

--建表ddlcreate table employee (emp_id smallint unsigned not null auto_increment,  fname varchar(20) not null,  lname varchar(20) not null,  start_date date not null,  end_date date,  superior_emp_id smallint unsigned,  dept_id smallint unsigned,  title varchar(20),  assigned_branch_id smallint unsigned,  constraint fk_e_emp_id     foreign key (superior_emp_id) references employee (emp_id),  constraint fk_dept_id    foreign key (dept_id) references department (dept_id),  constraint fk_e_branch_id    foreign key (assigned_branch_id) references branch (branch_id),  constraint pk_employee primary key (emp_id) );--查看索引信息mysql> show index from employee \G*************************** 1. row ***************************        Table: employee   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: emp_id    Collation: A  Cardinality: 18     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:Index_comment:*************************** 2. row ***************************        Table: employee   Non_unique: 1     Key_name: fk_e_emp_id Seq_in_index: 1  Column_name: superior_emp_id    Collation: A  Cardinality: 18     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment:Index_comment:*************************** 3. row ***************************        Table: employee   Non_unique: 1     Key_name: fk_dept_id Seq_in_index: 1  Column_name: dept_id    Collation: A  Cardinality: 6     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment:Index_comment:*************************** 4. row ***************************        Table: employee   Non_unique: 1     Key_name: fk_e_branch_id Seq_in_index: 1  Column_name: assigned_branch_id    Collation: A  Cardinality: 9     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment:Index_comment:4 rows in set (0.00 sec)mysql>