发表于 2009-1-5 19:48
| 
Mysql寮鍙戜腑鐨勫閿笌鍙傜収瀹屾暣鎬
鍙戝竷: 2008-7-01 09:34 | 浣滆: admin | 鏌ョ湅: 3娆
Mysql寮鍙戜腑鐨勫閿笌鍙傜収瀹屾暣鎬
鐑
銆愬瓧浣擄細灏 澶с
Mysql寮鍙戜腑鐨勫閿笌鍙傜収瀹屾暣鎬
浣滆咃細涓嶈 鏂囩珷鏉ユ簮锛氱綉缁 鐐瑰嚮鏁帮細525 鏇存柊鏃堕棿锛2007-1-21 
鍙傜収瀹屾暣鎬(Referential integrity)鏄暟鎹簱璁捐涓竴涓噸瑕佺殑姒傚康銆傚湪绯荤粺涓嶅悓鐨勫垪琛ㄤ腑锛屽綋鏁版嵁搴撴墍鏈夊弬鐓у悎娉曟垨闈炲悎娉曞叧鑱旀椂閮戒細娑夊強鍒板弬鐓у畬鏁存с傚綋鍙傜収瀹屾暣鎬у瓨鍦ㄦ椂锛屼换浣曚笌涓嶅瓨鍦ㄨ褰曠殑鍏宠仈灏嗗彉寰楁棤鏁堝寲锛岀敱姝ゅ彲闃叉鐢ㄦ埛鍑虹幇鍚勭閿欒锛屼粠鑰屾彁渚涙洿涓哄噯纭拰瀹炵敤鐨勬暟鎹簱銆
鍙傜収瀹屾暣鎬ч氬父閫氳繃澶栭敭(foreign key)鐨勪娇鐢ㄨ岃骞挎硾搴旂敤銆傞暱涔呬互鏉ワ紝娴佽宸ュ叿寮婧怰DBMS MySQL骞舵病鏈夋敮鎸佸閿紝鍘熷洜鏄繖绉嶆敮鎸佸皢浼氶檷浣嶳DBMS鐨勯熷害鍜屾ц兘銆傜劧鑰岋紝鐢变簬寰堝鐢ㄦ埛瀵瑰弬鐓у畬鏁存х殑浼樼偣鍊嶆劅鍏磋叮锛屾渶杩慚ySQL鐨勪笉鍚岀増鏈兘閫氳繃鏂癐nnoDB鍒楄〃寮曟搸鏀寔澶栭敭銆傜敱姝わ紝鍦ㄦ暟鎹簱缁勬垚鐨勫垪琛ㄤ腑淇濇寔鍙傜収瀹屾暣鎬у皢鍙樺緱闈炲父绠鍗曘
涓轰簡寤虹珛涓や釜MySQL琛ㄤ箣闂寸殑涓涓閿叧绯伙紝蹇呴』婊¤冻浠ヤ笅涓夌鎯呭喌锛
涓や釜琛ㄥ繀椤绘槸InnoDB琛ㄧ被鍨嬨
浣跨敤鍦ㄥ閿叧绯荤殑鍩熷繀椤讳负绱㈠紩鍨(Index)銆
浣跨敤鍦ㄥ閿叧绯荤殑鍩熷繀椤讳笌鏁版嵁绫诲瀷鐩镐技銆
渚嬪瓙鏄悊瑙ｄ互涓婅鐐圭殑鏈濂芥柟娉曘傚琛ˋ鎵绀猴紝寤虹珛涓や釜琛紝鍏朵腑涓涓垪鍑哄姩鐗╃绫诲強鐩稿簲鐨勪唬鐮(琛ㄥ悕涓猴細species)锛屽彟涓琛ㄥ垪鍑哄姩鐗╁洯涓殑鍔ㄧ墿(琛ㄥ悕涓猴細zoo)銆傜幇鍦紝鎴戜滑鎯抽氳繃species鍏宠仈杩欎袱涓〃锛屾墍浠ユ垜浠彧闇瑕佹帴鍙楀拰淇濆瓨zoo琛ㄤ腑鍖呭惈species琛ㄤ腑鐨勫悎娉曞姩鐗╃殑鍏ュ彛鍒版暟鎹簱涓
琛ˋ
MySQL> CREATE TABLE species (id TINYINT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)
MySQL> INSERT INTO species VALUES (1, 'orangutan'), (2, 'elephant'), (3, 'hippopotamus'), (4, 'yak');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE zoo (id INT(4) NOT NULL, name VARCHAR(50) NOT NULL, FK_species TINYINT(4) NOT NULL, INDEX (FK_species), FOREIGN KEY (FK_species) REFERENCES species (id), PRIMARY KEY(id)) ENGINE=INNODB;
娉ㄦ剰:瀵逛簬闈濱nnoDB琛紝 FOREIGN KEY 璇彞灏嗚蹇界暐銆
鐜板湪锛宖ieldszoo.species涓巗pecies.id 涔嬮棿瀛樺湪涓涓閿叧绯汇傚彧鏈夌浉搴旂殑zoo.specie涓巗pecies.idfield鐨勪竴涓肩浉鍖归厤锛屽姩鐗╄〃涓殑鍏ュ彛鎵嶅彲琚闂備互涓嬬殑杈撳嚭鍗虫紨绀轰簡褰撲綘鎯宠緭鍏ヤ竴涓狧arry Hippopotamus璁板綍锛岃屼娇鐢ㄥ埌涓嶅悎娉曠殑species浠ｇ爜锛
mysql> INSERT INTO zoo VALUES (1, 'Harry', 5);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
杩欓噷锛孧ySQL鏍告煡species琛ㄤ互鏌ョ湅species浠ｇ爜鏄惁瀛樺湪锛屽鏋滃彂鐜颁笉瀛樺湪锛屽氨鎷掔粷璇ヨ褰曘傚綋浣犺緭鍏ユ纭唬鐮佺殑锛屽彲浠ヤ笌浠ヤ笂鍋氭瘮杈冦
mysql> INSERT INTO zoo VALUES (1, 'Harry', 3);
Query OK, 1 row affected (0.06 sec)
杩欓噷锛孧ySQL鏍告煡species琛ㄤ互鏌ョ湅species浠ｇ爜鏄惁瀛樺湪锛屽綋鍙戠幇瀛樺湪锛屽厑璁歌褰曚繚瀛樺湪zoo琛ㄤ腑銆
涓轰簡鍒犻櫎涓涓閿叧绯伙紝棣栧厛浣跨敤SHOW CREATE TABLE鎵惧嚭InnoDB鐨勫唴閮ㄦ爣绛撅紝濡傝〃B鎵绀猴細
琛 B
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| zoo | CREATE TABLE `zoo` (
`id` int(4) NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
`FK_species` tinyint(4) NOT NULL default '0',
KEY `FK_species` (`FK_species`),
CONSTRAINT `zoo_ibfk_1` FOREIGN KEY (`FK_species`)
REFERENCES `species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------+
鐒跺悗浣跨敤甯︽湁DROP FOREIGN KEY 璇彞鐨凙LTER TABLE鍛戒护锛屽浠ヤ笅锛
mysql> ALTER TABLE zoo DROP FOREIGN KEY zoo_ibfk_1;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0
涓轰簡灏嗕竴涓閿坊鍔犲埌涓涓幇鎴愮殑琛ㄤ腑锛屼娇鐢ˋDD FOREIGN KEY鐨 ALTER TABLE璇彞鎸囧畾鍚堥傜殑鍩熶綔涓轰竴涓閿細
mysql> ALTER TABLE zoo ADD FOREIGN KEY (FK_species) REFERENCES species (id);
