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

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

举例：

--建表ddl

create 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>