引用另一个表中的属性组(主码)作为外键,一个正常,另一个无法添加外键,这是为什么?
这个问题纠结了两天了……为什么为什么为什么?????????
CREATE TABLE `match` (
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`matchGoals` int(11) DEFAULT NULL,
PRIMARY KEY (`matchNO`,`place`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;CREATE TABLE `join` (
`teamNameJ` varchar(50) NOT NULL,
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`result` varchar(50) DEFAULT NULL,
PRIMARY KEY (`teamNameJ`,`matchNO`,`place`),
KEY `matchNO` (`matchNO`),
KEY `place` (`place`),
CONSTRAINT `matchNO` FOREIGN KEY (`matchNO`) REFERENCES `match` (`matchNO`), //正常
CONSTRAINT `teamNameJ` FOREIGN KEY (`teamNameJ`) REFERENCES `team` (`teamName`),//正常
CONSTRAINT `place` FOREIGN KEY (`place`) REFERENCES `match` (`place`) //place外键无法插入
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
这个问题纠结了两天了……为什么为什么为什么?????????
CREATE TABLE `match` (
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`matchGoals` int(11) DEFAULT NULL,
PRIMARY KEY (`matchNO`,`place`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;CREATE TABLE `join` (
`teamNameJ` varchar(50) NOT NULL,
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`result` varchar(50) DEFAULT NULL,
PRIMARY KEY (`teamNameJ`,`matchNO`,`place`),
KEY `matchNO` (`matchNO`),
KEY `place` (`place`),
CONSTRAINT `matchNO` FOREIGN KEY (`matchNO`) REFERENCES `match` (`matchNO`), //正常
CONSTRAINT `teamNameJ` FOREIGN KEY (`teamNameJ`) REFERENCES `team` (`teamName`),//正常
CONSTRAINT `place` FOREIGN KEY (`place`) REFERENCES `match` (`place`) //place外键无法插入
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
这样写就对了:
CREATE TABLE `join` (
`teamNameJ` varchar(50) NOT NULL,
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`result` varchar(50) DEFAULT NULL,
PRIMARY KEY (`teamNameJ`,`matchNO`,`place`),
KEY `matchNO1` (`matchNO`),
KEY `place` (`place`),
CONSTRAINT `matchNO` FOREIGN KEY (`matchNO`, `place`) REFERENCES `match` (`matchNO`, `place`),
CONSTRAINT `teamNameJ` FOREIGN KEY (`teamNameJ`) REFERENCES `team` (`teamName`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
FOREIGN KEY (matchNO,place) REFERENCES `match` (matchNO,place)
CREATE TABLE `join` (
`teamNameJ` varchar(50) NOT NULL,
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`result` varchar(50) DEFAULT NULL,
PRIMARY KEY (`teamNameJ`,`matchNO`,`place`),
KEY `matchNO` (`matchNO`),
KEY `place` (`place`),
CONSTRAINT `matchNO` FOREIGN KEY (`matchNO`) REFERENCES `match` (`matchNO`), //正常
CONSTRAINT `teamNameJ` FOREIGN KEY (`teamNameJ`) REFERENCES `team` (`teamName`),//正常
CONSTRAINT `place` FOREIGN KEY (matchNO,`place`) REFERENCES `match` (matchNO,`place`) //place外键无法插入
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
CREATE TABLE `match` (
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`matchGoals` int(11) DEFAULT NULL,
PRIMARY KEY (`matchNO`,`place`),
KEY (`place`),
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
子表的外键必须依赖主表的主键的全部,应为主表的主键建立索引。
CREATE TABLE `join` (
`teamNameJ` varchar(50) NOT NULL,
`matchNO` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`result` varchar(50) DEFAULT NULL,
PRIMARY KEY (`teamNameJ`,`matchNO`,`place`),
KEY `mp` (`matchNO`,`place`),
CONSTRAINT `mp` FOREIGN KEY (`matchNO`, `place`) REFERENCES `match` (`matchNO`, `place`),
CONSTRAINT `teamNameJ` FOREIGN KEY (`teamNameJ`) REFERENCES `team` (`teamName`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;