MySQL - Adding a Constraint and Foreign Key

Question

I am having trouble establishing foreign keys for a database I am creating. The code seems to run, but when I query the table it does not return what I thought was the foreign key.

Here is what I am working with:

This is a small snippet of my "Crafting" table:

idCrafting    Crafting_Recipe       Item1            ITEM1_ID
1             Copper_Bar            Copper_Ore       NULL       
2             Gold_Bar              Gold_Ore         NULL

Here is the Items table I am working with:

Item_id     Item_Name
1           Battery_Pack
2           Clay
3           Coal
4           Copper_Ore
5           Copper_Bar

This is how I am trying to create a foreign key. The Crafting table has three Items, represented by Item1, Item2, and Item3, and columns for ITEM1_ID, ITEM2_ID, and ITEM3_ID. I want to create foreign keys that will populate the ITEM_ID columns with the appropriate ITEM_ids from the Items table.

ALTER TABLE StardewValley.Crafting
    ADD CONSTRAINT Item1_fk_Crafting
    FOREIGN KEY (ITEM1_ID) REFERENCES Items (Item_id);

This is how I created the original Crafting table:

CREATE TABLE IF NOT EXISTS StardewValley.Crafting (
  idCrafting INT NOT NULL AUTO_INCREMENT,
  Crafting_Recipe VARCHAR(45) NOT NULL,
  Item1 VARCHAR(45) NULL,
  Item2 VARCHAR(45) NULL,
  Item3 VARCHAR(45) NULL,
  PRIMARY KEY (`idCrafting`));

I have several other tables that use primary keys from several tables and I can not get it to work properly. I have been struggling to get this one to work which only references one other table.

Thanks!

edit:

I forgot to add that I tried adding Item ids to the Crafting table as well:

ALTER TABLE StardewValley.Crafting ADD ITEM1_ID INT;
ALTER TABLE StardewValley.Crafting ADD ITEM2_ID INT;
ALTER TABLE StardewValley.Crafting ADD ITEM3_ID INT;
ALTER TABLE StardewValley.Crafting ADD Item_id INT;

Which did not seem to do anything.

Are you saying that you want to see `Copper_Ore` in column `Item1` when `Item_id` is `4` in `Crafting` table? That's really not a good idea. Please don't span the definition of `Item_Name` across two tables. Only including `Item_id` as a reference should be enough. And if you're to get the `Item_Name` for any other purposes, use `JOIN` in the `SELECT` query. — Dhruv Saxena, Apr 18 '17 at 22:57
I was trying to create a table that represents a relationship that contains the Items with their names and ids that are present in Crafting recipes. I tried querying with a JOIN between crafting and items tables, but it did not work. I can try that again to see if it works, then that would be better. — Babeeshka, Apr 18 '17 at 23:02
Well, if the Database is normalized, maintaining and scaling it would become much more easier. Please have a look at this [Q&A](http://stackoverflow.com/q/2923809/2298301) where a many-to-many relationship is discussed (the `Product` / `Company` relationship might help you draw some parallels with your scenario). Also then this [post](http://stackoverflow.com/q/3486644/2298301) illustrates how `SELECT` queries may be written for many-to-many relationships. — Dhruv Saxena, Apr 18 '17 at 23:09

score 2 · Answer 1 · answered Apr 18 '17 at 22:51

Could you try updating the values before adding the FOREIGN KEY constraint, e.g:

UPDATE Crafting
SET ITEM1_ID = (SELECT Item_id FROM items WHERE Item_id = Item1 LIMIT 1);

This will make sure all the values point to correct keys in parent table. Also, I guess Item_id is already a primary key in Items table.

MySQL - Adding a Constraint and Foreign Key

1 Answers1