1. CREATE TABLE `tbl_tetsefs` (
`ver` varchar(20) default ' ',
`tet_id` char(35) NOT NULL,
`tag` varchar(255) default NULL,
`tet_name` varchar(255) default NULL,
`tet_test` char(35) default ' ',
`tet_prdid` char(35) NOT NULL default ' ',
PRIMARY KEY (`tet_id`),
UNIQUE KEY `index_tet_test` (`tet_test`),
KEY `fk_tet_prdid` (`tet_prdid`),
CONSTRAINT `fk_tet_prdid` FOREIGN KEY (`tet_prdid`) REFERENCES `tbl_orderproduct` (`prd_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;2.CREATE TABLE `tbl_orderproduct` (
`ord_id` char(35) NOT NULL default ' ',
`orp_id` char(35) NOT NULL default ' ',
`prd_id` char(35) NOT NULL default ' ',
`prd_name` varchar(255) default NULL,
PRIMARY KEY (`orp_id`),
KEY `FK_data_order` (`ord_id`),
KEY `FK_data_product` (`prd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3 . CREATE TABLE `tbl_product` (
`prd_id` char(35) NOT NULL,
`prd_name` varchar(225) NOT NULL default '',
`prd_desc` text,
PRIMARY KEY (`prd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
? ALTER TABLE tbl_tetsefs
DROP FOREIGN KEY fk_tet_prdid;语句能执行,但外键却没有删掉
?ALTER TABLE tbl_tetsefs ADD CONSTRAINT fk_tet_test FOREIGN KEY ( tet_test) REFERENCES tbl_product (prd_id) 能执行,但没建立外键
一句话,为什么外键的建立和删除不能成功?
`ver` varchar(20) default ' ',
`tet_id` char(35) NOT NULL,
`tag` varchar(255) default NULL,
`tet_name` varchar(255) default NULL,
`tet_test` char(35) default ' ',
`tet_prdid` char(35) NOT NULL default ' ',
PRIMARY KEY (`tet_id`),
UNIQUE KEY `index_tet_test` (`tet_test`),
KEY `fk_tet_prdid` (`tet_prdid`),
CONSTRAINT `fk_tet_prdid` FOREIGN KEY (`tet_prdid`) REFERENCES `tbl_orderproduct` (`prd_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;2.CREATE TABLE `tbl_orderproduct` (
`ord_id` char(35) NOT NULL default ' ',
`orp_id` char(35) NOT NULL default ' ',
`prd_id` char(35) NOT NULL default ' ',
`prd_name` varchar(255) default NULL,
PRIMARY KEY (`orp_id`),
KEY `FK_data_order` (`ord_id`),
KEY `FK_data_product` (`prd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3 . CREATE TABLE `tbl_product` (
`prd_id` char(35) NOT NULL,
`prd_name` varchar(225) NOT NULL default '',
`prd_desc` text,
PRIMARY KEY (`prd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
? ALTER TABLE tbl_tetsefs
DROP FOREIGN KEY fk_tet_prdid;语句能执行,但外键却没有删掉
?ALTER TABLE tbl_tetsefs ADD CONSTRAINT fk_tet_test FOREIGN KEY ( tet_test) REFERENCES tbl_product (prd_id) 能执行,但没建立外键
一句话,为什么外键的建立和删除不能成功?
+-------------+-----------------------------------------------------------------
| Table | Create Table
+-------------+-----------------------------------------------------------------
| tbl_tetsefs | CREATE TABLE `tbl_tetsefs` (
`ver` varchar(20) DEFAULT ' ',
`tet_id` char(35) NOT NULL,
`tag` varchar(255) DEFAULT NULL,
`tet_name` varchar(255) DEFAULT NULL,
`tet_test` char(35) DEFAULT '',
`tet_prdid` char(35) NOT NULL DEFAULT '',
PRIMARY KEY (`tet_id`),
UNIQUE KEY `index_tet_test` (`tet_test`),
KEY `fk_tet_prdid` (`tet_prdid`),
CONSTRAINT `fk_tet_prdid` FOREIGN KEY (`tet_prdid`) REFERENCES `tbl_orderprodu
ct` (`prd_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------
1 row in set (0.00 sec)mysql>
mysql> ALTER TABLE tbl_tetsefs
-> DROP FOREIGN KEY fk_tet_prdid;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql>
mysql> show create table tbl_tetsefs;
+-------------+------------------------------
| Table | Create Table
+-------------+------------------------------
| tbl_tetsefs | CREATE TABLE `tbl_tetsefs` (
`ver` varchar(20) DEFAULT ' ',
`tet_id` char(35) NOT NULL,
`tag` varchar(255) DEFAULT NULL,
`tet_name` varchar(255) DEFAULT NULL,
`tet_test` char(35) DEFAULT '',
`tet_prdid` char(35) NOT NULL DEFAULT '',
PRIMARY KEY (`tet_id`),
UNIQUE KEY `index_tet_test` (`tet_test`),
KEY `fk_tet_prdid` (`tet_prdid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+------------------------------
1 row in set (0.00 sec)mysql>
ALTER TABLE tbl_tetsefs
DROP FOREIGN KEY fk_tet_prdid;
SHOW CREATE TABLE tbl_tetsefs;
结果还是存在外键
| tbl_tetsefs | CREATE TABLE `tbl_tetsefs` (
`ver` varchar(20) default '',
`tet_id` char(35) NOT NULL,
`tag` varchar(255) default NULL,
`tet_name` varchar(255) default NULL,
`tet_order` int(11) default NULL,
`tet_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`tet_test2` decimal(11,0) default '0',
`tet_test` char(35) NOT NULL default '0',
`tet_rr` decimal(12,1) NOT NULL,
`tet_prdid` char(35) NOT NULL default '',
`fsd` varchar(11) default NULL,
PRIMARY KEY (`tet_id`),
UNIQUE KEY `index_tet_test` (`tet_test`),
KEY `fk_tet_prdid` (`tet_prdid`),
CONSTRAINT `fk_tet_prdid` FOREIGN KEY (`tet_prdid`) REFERENCES `tbl_orderproduct` (`prd_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> ALTER TABLE tbl_tetsefs
-> DROP FOREIGN KEY fk_tet_prdid;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tbl_tetsefs;
| Table | Create Table
| tbl_tetsefs | CREATE TABLE `tbl_tetsefs` (
`ver` varchar(20) default '',
`tet_id` char(35) NOT NULL,
`tag` varchar(255) default NULL,
`tet_name` varchar(255) default NULL,
`tet_order` int(11) default NULL,
`tet_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`tet_test2` decimal(11,0) default '0',
`tet_test` char(35) NOT NULL default '0',
`tet_rr` decimal(12,1) NOT NULL,
`tet_prdid` char(35) NOT NULL default '',
`fsd` varchar(11) default NULL,
PRIMARY KEY (`tet_id`),
UNIQUE KEY `index_tet_test` (`tet_test`),
KEY `fk_tet_prdid` (`tet_prdid`),
CONSTRAINT `fk_tet_prdid` FOREIGN KEY (`tet_prdid`) REFERENCES `tbl_orderproduct` (`prd_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.47-community |
+------------------+
1 row in set (0.12 sec)
+--------------------+
| version() |
+--------------------+
| 5.1.6-alpha-nt-max |
+--------------------+
1 row in set (0.00 sec)