CODEWITHSUNDEEP
mysqldatabaseforeign-keysforeign-key-relationship
Babeeshka
Babeeshka

Reputation: 105

MySQL - Adding a Constraint and Foreign Key

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.

Upvotes: 1

Views: 150

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

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.

Upvotes: 2

Related Questions