mysql 外键的记载

mysql> show create table tb_dept1;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_dept1 | CREATE TABLE `tb_dept1` (
  `id` int(11) NOT NULL,
  `name` varchar(22) CHARACTER SET latin1 NOT NULL,
  `location` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table tb_emp5
    ->     (
    ->      id int(11) primary key,
    ->      name varchar(25),
    ->      deptId int(11),
    ->      salary float,
    ->      constraint fk_emp_dept1 foreign key (deptId) references tb_dept1(id)
    ->      ) ENGINE=InnoDB Default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into tb_dept1 values('1','技术部','xxxxxxxx');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_emp5 values('1','xx','2','3300');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`lisheng`.`tb_emp5`, CONSTRAINT `fk_emp_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`))
mysql> insert into tb_emp5 values('1','xx','1','3300');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tb_emp5;
+----+--------+--------+--------+
| id | name   | deptId | salary |
+----+--------+--------+--------+
|  1 | xx |      1 |   3300 |
+----+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from tb_dept1;
+----+-----------+--------------------+
| id | name      | location           |
+----+-----------+--------------------+
|  1 | 技术部     |   xxxxxxxxxx |
+----+-----------+--------------------+
1 row in set (0.00 sec)

说明一下，mysql默认是不支持外键约束的，也就是mysql默认的myisam 存储类型是不支持外键约束的，如果要支持就要用innodb类型,两个表多要是innodb类型的，否则是会报错的。