SQL/建立数据库/主键&外键
SET FOREIGN_KEY_CHECKS=0;#取消外键约束,Mysql中如果表和表之间建立的外键约束,则无法删除表及修改表结构。 DROP TABLE IF EXISTS `custinfo`; CREATE TABLE `custinfo` ( `custID` varchar(19) NOT NULL COMMENT '客户号', `name` varchar(10) NOT NULL COMMENT '姓名', `sex` varchar(10) NOT NULL COMMENT '性别', `phone` varchar(20) NOT NULL COMMENT '手机号', `ID` varchar(19) DEFAULT NULL COMMENT '身份证号', `districtID` varchar(19) DEFAULT NULL COMMENT '区ID', `age` varchar(6) NOT NULL COMMENT '年龄', PRIMARY KEY (`custID`), KEY `FK_districtID_1` (`districtID`), CONSTRAINT `FK_districtID_1` FOREIGN KEY (`districtID`) REFERENCES `area_dim` (`districtID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户信息表'; -- ---------------------------- -- Records of custinfo -- ---------------------------- BEGIN; INSERT INTO `custinfo` VALUES ('1', '闫一一', '男', '13645667783', '567891321242345618', '10101', '18'); INSERT INTO `custinfo` VALUES ('2', '朱三枪', '男', '13645667890', '567891984242345618', '20101', '28'); INSERT INTO `custinfo` VALUES ('3', '姜为民', '男', '13642345799', '567891322489345618', '20101', '18'); INSERT INTO `custinfo` VALUES ('4', '刘德华', '男', '13612345690', '567891989909345618', '30201', '28'); INSERT INTO `custinfo` VALUES ('5', '张三三', '女', '13643455799', '567891322489349898', '30201', '18'); INSERT INTO `custinfo` VALUES ('6', '刘思思', '男', '13643215690', '567891989909349007', '30201', '28'); COMMIT; -- ---------------------------- -- Table structure for `cardinfo` -- ---------------------------- DROP TABLE IF EXISTS `cardinfo`; CREATE TABLE `cardinfo` ( `acct_no` varchar(19) NOT NULL COMMENT '账户', `balance` varchar(19) NOT NULL COMMENT '账号余额', `acct_status` varchar(19) NOT NULL COMMENT '账号状态', `openDate` varchar(19) NOT NULL COMMENT '开卡时间', `openmoney` varchar(19) NOT NULL COMMENT '开卡金额', `custID` varchar(19) NOT NULL COMMENT '客户号', `districtID` varchar(19) NOT NULL COMMENT '开户所在区ID', PRIMARY KEY (`acct_no`), KEY `FK_districtID` (`districtID`), KEY `FK_custID` (`custID`), CONSTRAINT `FK_districtID` FOREIGN KEY (`districtID`) REFERENCES `area_dim` (`districtID`), CONSTRAINT `FK_custID` FOREIGN KEY (`custID`) REFERENCES `custinfo` (`custID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='银行卡信息表'; -- ---------------------------- -- Records of cardinfo -- ---------------------------- BEGIN; INSERT INTO `cardinfo` VALUES ('1010357612121001', '888888', '1', '2019-07-19 08:49:37', '1.00', '1', '10101'); INSERT INTO `cardinfo` VALUES ('1010357612121002', '888888', '1', '2019-07-18 08:49:37', '2.00', '2', '20101'); INSERT INTO `cardinfo` VALUES ('1010357612121003', '888888', '0', '2019-07-19 08:49:37', '3.00', '3', '20101'); INSERT INTO `cardinfo` VALUES ('1010357612121004', '888888', '1', '2019-07-19 08:49:37', '1.00', '1', '10101'); INSERT INTO `cardinfo` VALUES ('1010357612121005', '888888', '1', '2019-07-18 08:49:37', '2.00', '2', '20101'); COMMIT; DROP TABLE IF EXISTS `area_dim`; CREATE TABLE `area_dim` ( `districtID` varchar(19) NOT NULL COMMENT '区域ID', `area_name` varchar(19) NOT NULL COMMENT '区域名', `city_id` varchar(19) NOT NULL COMMENT '城市ID', `city_name` varchar(19) NOT NULL COMMENT '城市名', `province_id` varchar(19) DEFAULT NULL COMMENT '省ID', `province_name` varchar(19) DEFAULT NULL COMMENT '省名', PRIMARY KEY (`districtID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='区域码值表'; -- ---------------------------- -- Records of area_dim -- ---------------------------- BEGIN; INSERT INTO `area_dim` VALUES ('10101', '二七区', '101', '郑州市', '1', '河南省'); INSERT INTO `area_dim` VALUES ('20101', '高新区', '201', '青岛市', '2', '山东省'); INSERT INTO `area_dim` VALUES ('30201', '新区', '301', '石家庄市', '3', '河北省'); COMMIT; SET FOREIGN_KEY_CHECKS = 1; #设置外检约束
注:将同一个字段设置成2个表的外键时,2次的外键名应不同。
posted on 2019-07-24 23:07 Free_Sky01 阅读(1790) 评论(0) 编辑 收藏 举报