我想请教一下,外键是否能够以一对多的形式存在。例如:dept_master.dept_id(Primary Key) 对应 show_master.dept1(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept2(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept3(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept4(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept5(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept6(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept7(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept8(Foreign key)CREATE TABLE `show_master` (
`show_id` int(11) NOT NULL AUTO_INCREMENT,
`show_name` varchar(30) NOT NULL,
`show_cost` int(11) NOT NULL,
`show_frame_lable` varchar(30) NOT NULL,
`show_set_frame_lable` varchar(30) NOT NULL,
`show_set_bound_row` smallint(11) NOT NULL,
`show_set_bound_col` smallint(11) NOT NULL,
`show_unlock_level` int(11) NOT NULL,
`publish_img` varchar(20) NOT NULL,
`dept1` varchar(20) NOT NULL,
`dept2` varchar(20) NOT NULL,
`dept3` varchar(20) NOT NULL,
`dept4` varchar(20) NOT NULL,
`dept5` varchar(20) NOT NULL,
`dept6` varchar(20) NOT NULL,
`dept7` varchar(20) NOT NULL,
`dept8` varchar(20) NOT NULL,
`multi1` float NOT NULL,
`multi2` float NOT NULL,
`multi3` float NOT NULL,
`multi4` float NOT NULL,
`multi5` float NOT NULL,
`multi6` float NOT NULL,
`multi7` float NOT NULL,
`multi8` float NOT NULL,
PRIMARY KEY (`show_id`),
KEY `dept1` (`dept1`),
KEY `dept2` (`dept2`),
KEY `dept3` (`dept3`),
KEY `dept4` (`dept4`),
KEY `dept5` (`dept5`),
KEY `dept6` (`dept6`),
KEY `dept7` (`dept7`),
KEY `dept8` (`dept8`),
CONSTRAINT `show_master_ibfk_21` FOREIGN KEY (`dept1`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_22` FOREIGN KEY (`dept2`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_23` FOREIGN KEY (`dept3`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_24` FOREIGN KEY (`dept4`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_25` FOREIGN KEY (`dept5`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_26` FOREIGN KEY (`dept6`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_27` FOREIGN KEY (`dept7`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_28` FOREIGN KEY (`dept8`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;这样可以么,是否会对性能有影响。
dept_master.dept_id(Primary Key) 对应 show_master.dept2(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept3(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept4(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept5(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept6(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept7(Foreign key)
dept_master.dept_id(Primary Key) 对应 show_master.dept8(Foreign key)CREATE TABLE `show_master` (
`show_id` int(11) NOT NULL AUTO_INCREMENT,
`show_name` varchar(30) NOT NULL,
`show_cost` int(11) NOT NULL,
`show_frame_lable` varchar(30) NOT NULL,
`show_set_frame_lable` varchar(30) NOT NULL,
`show_set_bound_row` smallint(11) NOT NULL,
`show_set_bound_col` smallint(11) NOT NULL,
`show_unlock_level` int(11) NOT NULL,
`publish_img` varchar(20) NOT NULL,
`dept1` varchar(20) NOT NULL,
`dept2` varchar(20) NOT NULL,
`dept3` varchar(20) NOT NULL,
`dept4` varchar(20) NOT NULL,
`dept5` varchar(20) NOT NULL,
`dept6` varchar(20) NOT NULL,
`dept7` varchar(20) NOT NULL,
`dept8` varchar(20) NOT NULL,
`multi1` float NOT NULL,
`multi2` float NOT NULL,
`multi3` float NOT NULL,
`multi4` float NOT NULL,
`multi5` float NOT NULL,
`multi6` float NOT NULL,
`multi7` float NOT NULL,
`multi8` float NOT NULL,
PRIMARY KEY (`show_id`),
KEY `dept1` (`dept1`),
KEY `dept2` (`dept2`),
KEY `dept3` (`dept3`),
KEY `dept4` (`dept4`),
KEY `dept5` (`dept5`),
KEY `dept6` (`dept6`),
KEY `dept7` (`dept7`),
KEY `dept8` (`dept8`),
CONSTRAINT `show_master_ibfk_21` FOREIGN KEY (`dept1`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_22` FOREIGN KEY (`dept2`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_23` FOREIGN KEY (`dept3`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_24` FOREIGN KEY (`dept4`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_25` FOREIGN KEY (`dept5`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_26` FOREIGN KEY (`dept6`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_27` FOREIGN KEY (`dept7`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `show_master_ibfk_28` FOREIGN KEY (`dept8`) REFERENCES `dept_master` (`dept_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;这样可以么,是否会对性能有影响。
从性能上来说,索引肯定会影响插入、更新、删除的性能,但会显著提高有关查询的性能。