Home > Software design >  Insert with Foreign Key Contraints
Insert with Foreign Key Contraints

Time:10-25

CREATE TABLE makeModel(
    makeID INT NOT NULL DEFAULT '0',
    modelID INT NOT NULL DEFAULT '0',
    PRIMARY KEY (makeID , modelID),
    FOREIGN KEY (makeID) REFERENCES make(id),
    FOREIGN KEY (modelID) REFERENCES model(id)
    );

INSERT INTO makeModel (makeID)
    SELECT DISTINCT (make) FROM vehicle;

In the above code, I want to add the distinct values from column make from vehicles into column makeID in makeModel but I get the error

INSERT INTO makeModel (makeID) SELECT DISTINCT (make) FROM vehicle Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`dealership`.`makemodel`, CONSTRAINT `makemodel_ibfk_2` FOREIGN KEY (`modelID`) REFERENCES `model` (`ID`))

CodePudding user response:

You need to insert into the model field as well, since it's also a foreign key.

INSERT INTO makeModel(makeID, modelID)
SELECT DISTINCT make, model
FROM vehicle
  • Related