例如我们有两张表:
computer:
CREATE TABLE `computer` (
`id` int(11) NOT NULL auto_increment,
`cname` varchar(15) NOT NULL,
`CPU` varchar(15) NOT NULL,
`ROM` varchar(15) NOT NULL,
`hardpan` varchar(15) NOT NULL,
`repair` varchar(10) NOT NULL,
`useof` varchar(10) NOT NULL,
`ip` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)
user:
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(15) NOT NULL,
`pwd` varchar(15) NOT NULL,
`name` varchar(20) NOT NULL,
`address` varchar(20) default NULL,
`tel` varchar(11) default NULL,
`balance` double default NULL,
`permission` int(11) NOT NULL default '0',
`datemoney` double default NULL,
PRIMARY KEY (`id`)
)
这里需要设置两个外键:
CREATE TABLE `commentary` (
`id` int(11) NOT NULL auto_increment,
`userid` int(10) NOT NULL default '0',
`computerid` int(10) NOT NULL default '0',
`content` mediumtext NOT NULL,
`date` datetime default NULL,
PRIMARY KEY (`id`),
KEY `FK_commentary_1` USING BTREE (`userid`),
KEY `FK_commentary_2` USING BTREE (`computerid`),
CONSTRAINT `commentary_ibfk_1` FOREIGN KEY (`computerid`) REFERENCES `computer` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `commentary_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
)
注意最后两行中的ON DELETE CASCADE ON UPDATE NO ACTION
也即关联删除,但更新数据的时候则不行进行关联。
例如,当电脑A中有一条评论,评论中存放有该电脑的id,所以当删除此电脑是,则会关联删除这条电脑的评论选项。当然这里这是数据库中的约束,相同的想过也可以通过xxx.hbm.xml进行设置。