关于MySQL的外键问题

[myblessu]

在mysql的官方页面 http://dev.mysql.com/doc/mysql/en/example-Foreign_keys.html 上有个使用外键的例子.

大家请看该网站.按该页面的操作,查询shirt的结果如下.

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+

可是在我的机子上确实显示:

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 0 |
| 2 | dress | white | 0 |
| 3 | t-shirt | blue | 0 |
| 4 | dress | orange | 0 |
| 5 | polo | red | 0 |
| 6 | dress | blue | 0 |
| 7 | t-shirt | white | 0 |
+----+---------+--------+-------+


这说明外键根本没起作用,这是为什么?

后来我把那建表的语句改了一下,换成如下格式的,结果也是一样.

CREATE TABLE `person` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` char(60) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL default 't-shirt',
`color` enum('red','blue','orange','white','black') NOT NULL default 'red',
`owner` smallint(5) unsigned NOT NULL REFERENCES person(id),
PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

[fish21cn]

汗, mysql的bug被你们发现了...^_^

[多菜鸟]

偶按照上面的例子可以的。你的那个帖子里大家都说了，是不是你的版本有问题？

[myblessu]

我知道了,原来我是在php代码中执行,没得到正确结果,后来在DOS命令提示符下使用就可以了,

但现在又发现新问题了.

当我按那页面的代码测试后.
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+

,这结果是正确的,然后我用下面的php插入一条记录

mysql_query("INSERT INTO shirt VALUES(NULL,'dress','orange',LAST_INSERT_ID())");

,在这php执行后,我又转到DOS命令提示符下,在用

INSERT INTO shirt VALUES(NULL,'dress','orange',LAST_INSERT_ID());

语句执行了一下.结果现在

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
| 8 | dress | orange | 0 |
| 9 | dress | orange | 4 |
+----+---------+--------+-------+

这owner的指怎么会为0或4呢.在person表的id中没有这两个值呀?

[satangf]

标记