CODEWITHSUNDEEP
mysqlmysql-workbench
casper
casper

Reputation: 1

Foreign Keys and importing data in MySQL workbench issues

I have looked everywhere and seen most of the threads pertaining to this issue but still can't get it to work for me.

This is what I made using Workbench. It's a real estate database:

DROP DATABASE IF EXISTS projet3;
CREATE DATABASE projet3;
USE projet3;

CREATE TABLE Localite (
Localite_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
Commune VARCHAR(50) NOT NULL,
Departement SMALLINT NOT NULL,
PRIMARY KEY (Localite_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Bien (
Bien_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
SurCar1 DOUBLE NOT NULL,
TypeLoc VARCHAR(15) NOT NULL,
NoPP SMALLINT NOT NULL,
Localite_ID SMALLINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (Bien_ID),
CONSTRAINT FK_Localite FOREIGN KEY (localite_ID) REFERENCES localite (Localite_ID) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Mutation (
Mutation_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
DateMutation DATETIME NOT NULL,
ValeurFonciere INT NOT NULL,
Bien_ID SMALLINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (Mutation_ID),
CONSTRAINT FK_Bien FOREIGN KEY (Bien_ID) REFERENCES bien (Bien_ID) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS Adresse;
CREATE TABLE Adresse (
Adresse_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
NoVoie SMALLINT NOT NULL,
TypeVoie VARCHAR(10) NOT NULL,
NomVoie VARCHAR(50) NOT NULL,
CodePostal INT NOT NULL,
Bien_ID SMALLINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (Adresse_ID),
CONSTRAINT FK_Bien FOREIGN KEY (Bien_ID) REFERENCES bien (Bien_ID) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;`

The corresponding model looks like this: EER model I had to change the relationship between Bien and Adresse to a 1 on 1, which I then forward engineered. I understand I could integrate the Adresse attributes into the Bien table but for the sake of the tasks I have to perform on it I have kept them separate.

However, when I then try to import data into any table with a foreign key the workbench refuses giving me this: Cannot add or update child row

I have also tried to simply create the tables, import the data and then create the foreign keys but I get the same result.

Any help would be appreciated!

Upvotes: 0

Views: 958

Answers (2)

nbk
nbk

Reputation: 49410

You problem is

that you have twice

CONSTRAINT FK_Bien FOREIGN KEY (Bien_ID) REFERENCES bien (Bien_ID) ON DELETE RESTRICT ON UPDATE CASCADE

As every Foreign key needs his unique name.

So rename the second in the table Adresse for example to FK_Bien1

Upvotes: 2

MontyPython
MontyPython

Reputation: 3002

Temporarily disable the foreign key checks after creating the tables & before loading the data by using the following command:

FOREIGN_KEY_CHECKS = 0

Once you load/import the data into the tables, you can enable the foreign key checks by setting it back to 1. Some of the use case of this parameter have been well documented on TablePlus's blog.

Upvotes: 0

Related Questions