首先我创建了两张表:表:books
Create TABLE IF NOT EXISTS `books` (
`book_id` smallint(6) NOT NULL auto_increment COMMENT '书籍编号',
`book_name` char(20) NOT NULL COMMENT '书名',
`book_author` char(20) NOT NULL COMMENT '作者',
`book_borrower` char(7) default NULL COMMENT '借阅者',
PRIMARY KEY (`book_id`),
INDEX (book_borrower)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;表:parts
Create TABLE IF NOT EXISTS `parts` (
`part_id` smallint(6) NOT NULL COMMENT '成员编号',
`part_name` varchar(6) NOT NULL COMMENT '成员名',
`part_mail` varchar(50) NOT NULL COMMENT '邮箱',
`part_pass` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`part_id`),
FOREIGN KEY(part_name) REFERENCES books(book_borrower) on delete cascade on update cascade
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; 为两个表插入数据:
Insert INTO `books` (`book_id`, `book_name`, `book_pic`, `book_author`, `book_pub`, `book_sort`, `book_owner`, `book_borrower`, `book_borrower_time`) VALUES
(1, '设计中的设计', '原研哉', 'UserA')
Insert INTO `parts` ( `part_id` , `part_name` , `part_mail` , `part_pass` ) VALUES ( '1', 'UserA', '[email protected]', '8636257' ); 哪么问题来了,我可以通过
Delete FROM books Where book_borrower = 'UserA'; 把主表和关联表的记录一下都删除了。
但是....我希望能够通过 update books set book_borrower='UserB' where Where book_borrower = 'UserA' 一下把主表和关联表的字段都跟新了,但是数据库报错了。
很是郁闷啊...达人快来帮助解释一下。谢谢了先。o(∩_∩)o...
Create TABLE IF NOT EXISTS `books` (
`book_id` smallint(6) NOT NULL auto_increment COMMENT '书籍编号',
`book_name` char(20) NOT NULL COMMENT '书名',
`book_author` char(20) NOT NULL COMMENT '作者',
`book_borrower` char(7) default NULL COMMENT '借阅者',
PRIMARY KEY (`book_id`),
INDEX (book_borrower)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;表:parts
Create TABLE IF NOT EXISTS `parts` (
`part_id` smallint(6) NOT NULL COMMENT '成员编号',
`part_name` varchar(6) NOT NULL COMMENT '成员名',
`part_mail` varchar(50) NOT NULL COMMENT '邮箱',
`part_pass` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`part_id`),
FOREIGN KEY(part_name) REFERENCES books(book_borrower) on delete cascade on update cascade
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; 为两个表插入数据:
Insert INTO `books` (`book_id`, `book_name`, `book_pic`, `book_author`, `book_pub`, `book_sort`, `book_owner`, `book_borrower`, `book_borrower_time`) VALUES
(1, '设计中的设计', '原研哉', 'UserA')
Insert INTO `parts` ( `part_id` , `part_name` , `part_mail` , `part_pass` ) VALUES ( '1', 'UserA', '[email protected]', '8636257' ); 哪么问题来了,我可以通过
Delete FROM books Where book_borrower = 'UserA'; 把主表和关联表的记录一下都删除了。
但是....我希望能够通过 update books set book_borrower='UserB' where Where book_borrower = 'UserA' 一下把主表和关联表的字段都跟新了,但是数据库报错了。
很是郁闷啊...达人快来帮助解释一下。谢谢了先。o(∩_∩)o...
Create TABLE IF NOT EXISTS `books` (
`book_id` smallint(6) NOT NULL auto_increment COMMENT '书籍编号',
`book_name` char(20) NOT NULL COMMENT '书名',
`book_author` char(20) NOT NULL COMMENT '作者',
`book_borrower` char(7) default NULL COMMENT '借阅者',
PRIMARY KEY (`book_id`),
INDEX (book_borrower)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;表:parts
Create TABLE IF NOT EXISTS `parts` (
`part_id` smallint(6) NOT NULL COMMENT '成员编号',
`part_name` varchar(7) NOT NULL COMMENT '成员名',
`part_mail` varchar(50) NOT NULL COMMENT '邮箱',
`part_pass` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`part_id`),
FOREIGN KEY(part_name) REFERENCES books(book_borrower) on delete cascade on update cascade
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
表:books
Create TABLE IF NOT EXISTS `books` (
`book_id` smallint(6) NOT NULL auto_increment COMMENT '书籍编号',
`book_name` char(20) NOT NULL COMMENT '书名',
`book_author` char(20) NOT NULL COMMENT '作者',
`book_borrower` char(7) default NULL COMMENT '借阅者',
PRIMARY KEY (`book_id`),
INDEX (book_borrower)
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; 表:parts
Create TABLE IF NOT EXISTS `parts` (
`part_id` smallint(6) NOT NULL COMMENT '成员编号',
`part_name` varchar(7) NOT NULL COMMENT '成员名', //就是这里,注意啦
`part_mail` varchar(50) NOT NULL COMMENT '邮箱',
`part_pass` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`part_id`),
FOREIGN KEY(part_name) REFERENCES books(book_borrower) on delete cascade on update cascade
)
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
字段长度该成一样的后,在本地测试通过
mysql> update books set book_borrower = 'b';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0