CREATE TABLE tb_month_report_chapter_main(
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
`record_month` char(7) NOT NULL ,
`customer_id` int(11) NOT NULL,
PRIMARY KEY(`id`),
UNIQUE KEY `record_month` (`record_month`, `customer_id`) #创建了联合唯一约束 当月份和用户id有重复时会报错
);
CREATE TABLE tb_month_report_chapter_main(
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
`record_month` char(7) NOT NULL ,
`customer_id` int(11) NOT NULL,
PRIMARY KEY(`id`),
UNIQUE KEY `record_month` (`record_month`, `customer_id`)
);
CREATE TABLE tb_month_report_chapter_text(
`id` INTEGER(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
`main_id` INTEGER(11),
`chapter` VARCHAR(10) NOT NULL,
`text_type` VARCHAR(10) NOT NULL,
`text_content` VARCHAR(255) DEFAULT NULL,
FOREIGN KEY(`main_id`) REFERENCES tb_month_report_chapter_main(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE tb_month_report_chapter_img(
`id` INTEGER(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
`main_id` INTEGER(11),
`chapter` VARCHAR(10) NOT NULL,
`img_type` VARCHAR(10) NOT NULL,
`img` LONGBLOB DEFAULT NULL,
FOREIGN KEY(`main_id`) REFERENCES tb_month_report_chapter_main(id) ON DELETE CASCADE ON UPDATE CASCADE
);
这个表的关系,通过 主表的唯一标识, 月份和用户Id确定唯一,而文字和图片保存在另外两个表。