表一:
CREATE TABLE `whyposts` (
`ID` bigint(20) NOT NULL auto_increment,
`post_author` varchar(60) character set utf8 collate utf8_unicode_ci NOT NULL,
`post_title` text character set utf8 collate utf8_unicode_ci NOT NULL,
`post_content` longtext character set utf8 collate utf8_unicode_ci NOT NULL,
`post_category` varchar(64) character set utf8 collate utf8_unicode_ci NOT NULL,
`post_date` datetime NOT NULL,
`post_modified` datetime NOT NULL,
`post_excerpt` text character set utf8 collate utf8_unicode_ci NOT NULL,
`post_hits` int(11) NOT NULL,
`comment_count` int(11) NOT NULL,
`post_view_level` int(11) NOT NULL,
`comment_status` int(11) NOT NULL,
PRIMARY KEY (`ID`),
FOREIGN KEY ( `post_category` ) REFERENCES whypost_categroy(category_name) ON DELETE CASCADE
)
表二CREATE TABLE `whypost_categroy` (
`ID` bigint(20) NOT NULL,
`category_name` varchar(64) character set utf8 NOT NULL,
`categroy_info` varchar(64) character set utf8 NOT NULL,
`create_date` datetime NOT NULL,
PRIMARY KEY (`ID`)
)
使二者关联,删除表二中的记录则相应删除表1中的所有属于表二该记录分类的文章,但是无法创建外键 怎么回事儿啊
CREATE TABLE `whyposts` (
`ID` bigint(20) NOT NULL auto_increment,
`post_author` varchar(60) character set utf8 collate utf8_unicode_ci NOT NULL,
`post_title` text character set utf8 collate utf8_unicode_ci NOT NULL,
`post_content` longtext character set utf8 collate utf8_unicode_ci NOT NULL,
`post_category` varchar(64) character set utf8 collate utf8_unicode_ci NOT NULL,
`post_date` datetime NOT NULL,
`post_modified` datetime NOT NULL,
`post_excerpt` text character set utf8 collate utf8_unicode_ci NOT NULL,
`post_hits` int(11) NOT NULL,
`comment_count` int(11) NOT NULL,
`post_view_level` int(11) NOT NULL,
`comment_status` int(11) NOT NULL,
PRIMARY KEY (`ID`),
FOREIGN KEY ( `post_category` ) REFERENCES whypost_categroy(category_name) ON DELETE CASCADE
)
表二CREATE TABLE `whypost_categroy` (
`ID` bigint(20) NOT NULL,
`category_name` varchar(64) character set utf8 NOT NULL,
`categroy_info` varchar(64) character set utf8 NOT NULL,
`create_date` datetime NOT NULL,
PRIMARY KEY (`ID`)
)
使二者关联,删除表二中的记录则相应删除表1中的所有属于表二该记录分类的文章,但是无法创建外键 怎么回事儿啊
http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-foreign-key-constraints
15.2.6.4.外键约束
InnoDB也支持外键约束。InnoDB中对外键约束定义的语法看起来如下:[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
外键定义服从下列情况:· 所有tables必须是InnoDB型,它们不能是临时表。· 在引用表中,必须有一个索引,外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在,它必须在 引用表里被自动创建。· 在引用表中,必须有一个索引,被引用的列以同样的顺序被列在其中作为第一列。如果引用的不是主键,请为其建立一个索引,通常来说,建议使用主键做为外键。
建立外键。
建立外键。
*************************** 1. row ***************************
Table: whypost_categroy
Create Table: CREATE TABLE `whypost_categroy` (
`ID` bigint(20) NOT NULL,
`category_name` varchar(64) NOT NULL,
`categroy_info` varchar(64) NOT NULL,
`create_date` datetime NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)mysql> SHOW CREATE TABLE whypostS\G
*************************** 1. row ***************************
Table: whypostS
Create Table: CREATE TABLE `whyposts` (
`ID` bigint(20) NOT NULL auto_increment,
`post_author` varchar(60) character set utf8 collate utf8_unicode_ci NOT NULL, `post_title` text character set utf8 collate utf8_unicode_ci NOT NULL,
`post_content` longtext character set utf8 collate utf8_unicode_ci NOT NULL,
`post_category` varchar(64) set utf8 collate utf8_unicode_ci NOT NULL,
`post_date` datetime NOT NULL,
`post_modified` datetime NOT NULL,
`post_excerpt` text character set utf8 collate utf8_unicode_ci NOT NULL,
`post_hits` int(11) NOT NULL,
`comment_count` int(11) NOT NULL,
`post_view_level` int(11) NOT NULL,
`comment_status` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `post_category` (`post_category`),
CONSTRAINT `whyposts_ibfk_1` FOREIGN KEY (`post_category`) REFERENCES `whypost
_categroy` (`category_name`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.25 sec)