只有InnoDB類(lèi)型的表才可以使用外鍵
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
)type=innoDB;
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL,
FOREIGN KEY (owner) REFERENCES PERSON(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (id)
)type=innoDB;
然后打開(kāi)MySQL Administrator終于看到 FOREIGN KEY有東西了，嘗試手工刪除后再用MySQL Administrator建立，成功：）
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
測試UPDATE關(guān)聯(lián)
UPDATE PERSON SET id=3 WHERE id=1;
SELECT * FROM shirt;
測試DELETE關(guān)鍵
DELETE FROM PERSON WHERE id=3;
SELECT * FROM shirt;
看到有文章簡(jiǎn)單使用owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),進(jìn)行連接，但這樣的連接好像沒(méi)任何意義，擔看到“MySQL 3.23.7-alpha手冊”有這樣一句話(huà)“一些應用程序要求他們存在”或者“插入強制約束”、“級聯(lián)刪除”、“級聯(lián)更新”這些特性是在MySQL甘個(gè)版本中才有吧
還有有文章說(shuō)要建立INDEX (owner) 才可以，但我經(jīng)測試并不需要，這可能是和MYSQL版本不同有關(guān)，我用的是MySQL4.1
PS.查看Mysql手冊才知道從MySQL 4.1.2開(kāi)始會(huì )自動(dòng)建立這個(gè)INDEX (owner)
參考：
http://dev.mysql.com/doc/mysql/e ... ey-constraints.html
