Primary key column does not allow null values

Question

I have a question concerning primary key or compound key. I have created a table where there are only two attributes and both are are primary keys.

The primary keys name are item_ID̀ andSName`

These two are foreign keys to another primary key (to two other tables) where: item_ID --> ID and SName --> SkillName

ITEM ID      SName

1        Basic weapons
2   
3   
4        Heavy weapon
5        Exotic Weapons
6        Heavy weapon
7        Power weapon

and the outcome is this:

The ID nr 2 and 3 does not need a skillname requirement. For that reason I have implemented like this (since a primary key is not allowed a 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');

Is that correctly done? or is there another way to code that the ID does not require a specific SName?

here is the code for the 3 tables:

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;

The idea is in table requirements item_ID ---> ID in item table and SName ---> SkillName in talents. for some reason i was able to make a foreign key from item_ID to ID but cant from SName to SkillName:

the values i have added to those tables are the followings (OBS that in the table requirements i have set ID 2 and 3 to '' since the dont need any talent to be able to select it. but i am unsure if i am allowed to use that method?

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');

Each table can only have one primary key. Are you saying your PK is a compound key consisting in `item_ID` and `sname`? — O. Jones, Dec 11 '13 at 13:50
Thats correct, or thats the idea. here is the code: 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; I have also tried to make a forign key for them both to two separated tables but some reason when i try on the SName it gives me an error code 1452 — PuchuKing33, Dec 11 '13 at 14:17
So there are three tables in your model? Please provide a short description of them. It looked like both `requirements.item_ID` and `requirements.SName` are foreign key to the same `item` table but your last comment just confused me ("foreign key (...) to two separated tables"). — RandomSeed, Dec 11 '13 at 16:16
@RandomSeed i will post the 3 tables my first post. hopefully it will clarify it a bit more:) — PuchuKing33, Dec 11 '13 at 20:43

score 0 · Answer 1 · answered Dec 12 '13 at 11:06

0

You are trying to deal with the situation where an Item has no Requirement. In other words, such an Item has no relation with a Talent: simply do not insert anything in Requirements for this Item!

answered Dec 12 '13 at 11:06

RandomSeed

29,301
6
52
87

Side question: what is the nature of the relationship between `Item` and `Talent`? If an `Item` requires **zero or one** `Talent` (no more), then your `Requirements` table is superfluous, the Requirement of an Item should be modeled with a simple foreign key in `Item` (referencing `Talent`). Other than this, your model is fine. – RandomSeed Dec 12 '13 at 11:06
As it stands only one talent is required but will add some more soon with more than one talent requirement per item, you said that if no requirement is needed simply do not insert anything in, but when i try to do that an error occurs. i have fixed most of the errors. but stuck in the table talents column talents_requiert do i insert null value or '' or nothing? and same goes with the table requierment, if no requirement is need for that specific item do i add ''? becuase i am not allowed to add null in a primary key? – PuchuKing33 Dec 12 '13 at 20:15
Do not insert any record at all in `requirements`. `SELECT item.* FROM item LEFT JOIN requirements ON requirements.item_id = item.id WHERE item_id IS NULL` would list items having no requirements. – RandomSeed Dec 13 '13 at 01:10

Primary key column does not allow null values

1 Answers1