主键列不允许空值

Question

我有一个关于主键或复合键的问题。我创建了一个表，其中只有两个属性，并且都是主键。

主键名称为item_ID̀ and SName`

这两个是另一个主键（到另外两个表）的外键，其中：CCD_ 2和CCD_ 3

商品ID名称1基本武器2.3.4重型武器5件奇异武器6重型武器7强力武器

结果是：

ID编号2和3不需要技能名称要求。出于这个原因，我实现了这样的（因为主键不允许为null）：

INSERT INTO Requierments (item_ID, SName) VALUES ( 
01, 'Basic weapons');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
02, null);
INSERT INTO Requierments (item_ID, SName) VALUES ( 
03, null);
INSERT INTO Requierments (item_ID, SName) VALUES ( 
04, 'Heavy weapon');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
05, 'Exotic Weapons');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
06, 'Heavy weapon');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
07, 'Power weapon');

这样做正确吗？或者有没有其他方法可以编码ID不需要特定的SName？

---

这是3张表的代码：

CREATE TABLE `talents` (
  `SkillName` varchar(30) NOT NULL DEFAULT '',
  `Bonus` varchar(30) DEFAULT NULL,
  `Description` varchar(70) DEFAULT NULL,
  `R_Str` int(11) DEFAULT NULL,
  `R_WS` int(11) DEFAULT NULL,
  `R_BS` int(11) DEFAULT NULL,
  `R_Fel` int(11) DEFAULT NULL,
  `R_Per` int(11) DEFAULT NULL,
  `R_Int` int(11) DEFAULT NULL,
  `R_Agi` int(11) DEFAULT NULL,
  `R_WP` int(11) DEFAULT NULL,
  `Talent_requiert` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`SkillName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `requierments` (
  `item_ID` int(11) NOT NULL DEFAULT '0',
  `SName` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`item_ID`,`SName`),
  CONSTRAINT `requierments_ibfk_1` FOREIGN KEY (`item_ID`) REFERENCES `item` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `item` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `Name_` varchar(30) DEFAULT NULL,
  `Weight` int(11) DEFAULT NULL,
  `Value_` int(11) DEFAULT NULL,
  `Availability` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个想法在表中需求item_ID-->ID在项目表中，SName--->SkillName在人才中。出于某种原因，我可以从item_ID到ID制作外键，但不能从SName到SkillName:

我添加到这些表格中的值如下（OBS，在表格要求中，我将ID 2和3设置为"，因为不需要任何天赋来选择它。但我不确定是否允许我使用这种方法？

INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'ambidextrous', 0, 'use either hand equally well', 0, 0, 0, 0, 0, 0, 30, 0, null);
INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'Frenzy', 0, 'enter psychotic rage to gain combat bonus', 0, 0, 0, 0, 0, 0, 0, 0, null);
INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'battle rage', 0, 'parry while frenzied', 0, 0, 0, 0, 0, 0, 0, 0, 'Frenzy');
INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'Exotic Weapons', 0, 'Player is able to use exotic weapons', 0, 0, 0, 0, 0, 0, 0, 0, 'Basic weapons');
INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'Basic weapons', 0, 'Player is able to use Basic weapons', 0, 0, 0, 0, 0, 0, 0, 0, null);
INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'Heavy weapon', 0, 'Player is able to use heavy weapons', 30, 0, 0, 0, 0, 0, 0, 0, null);
INSERT INTO Talents ( SkillName, Bonus, Description, R_Str, R_WS, R_BS, R_Fel, R_Per, R_Int, R_Agi, R_WP, Talent_requiert) VALUES (
 'Power weapon', 0, 'Player is able to use power weapons ', 40, 30, 0, 0, 0, 0, 0, 0, null);

INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
01, 'Las Carbine', 3, 75, 'Common' );
INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
02, 'Laspistol', 1, 50, 'Common' );
INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
03, 'Shotgun', 5, 60, 'average' );
INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
04, 'Heavy Bolter', 40, 2000, 'Very Rare' );
INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
05, 'Needle pistol', 2, 1250, 'Very Rare' );
INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
06, 'Chainsword', 6, 275, 'Rare' );
INSERT INTO Item ( ID, Name_, Weight, Value_, Availability) VALUES (
07, 'Power Sword', 4, 2500, 'Very Rare' );

INSERT INTO Requierments (item_ID, SName) VALUES ( 
01, 'Basic weapons');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
02, '');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
03, '');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
04, 'Heavy weapon');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
05, 'Exotic Weapons');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
06, 'Heavy weapon');
INSERT INTO Requierments (item_ID, SName) VALUES ( 
07, 'Power weapon');

Answer 1

您正在尝试处理Item没有Requirement的情况。换句话说，这样的Item与Talent没有关系：简单地说，不要在该Item的Requirements中插入任何内容！