0

I would like to add a foreign key to a column in my table Caracteristique with a reference from a column from a table Localisation:

Here are my tables :

mysql> DESCRIBE Caracteristique;
+-------------------+-------------------+------+-----+---------+----------------+
| Field             | Type              | Null | Key | Default | Extra          |
+-------------------+-------------------+------+-----+---------+----------------+
| id                | int unsigned      | NO   | PRI | NULL    | auto_increment |
| date              | date              | NO   |     | NULL    |                |
| valeurFonciere    | int unsigned      | NO   |     | NULL    |                |
| surfaceReelleBati | int unsigned      | NO   |     | NULL    |                |
| nbPiecePrincipale | int unsigned      | NO   |     | NULL    |                |
| communeID_id      | int unsigned      | NO   |     | NULL    |                |
| typeID_id         | smallint unsigned | NO   | MUL | NULL    |                |
+-------------------+-------------------+------+-----+---------+----------------+

mysql> DESCRIBE Localisation;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| communeID   | int unsigned | NO   | PRI | NULL    |       |
| commune     | varchar(50)  | NO   | UNI | NULL    |       |
| departement | int unsigned | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

mysql> SELECT id, valeurFonciere, communeID_id FROM Caracteristique LIMIT 10;
+----+----------------+--------------+
| id | valeurFonciere | communeID_id |
+----+----------------+--------------+
|  1 |          56000 |            0 |
|  2 |         165000 |            1 |
|  3 |         720000 |            2 |
|  4 |         429250 |            3 |
|  5 |         220900 |            4 |
|  6 |          42000 |            5 |
|  7 |         262000 |            6 |
|  8 |         190000 |            6 |
|  9 |         563130 |            2 |
| 10 |         535000 |            6 |
+----+----------------+--------------+

mysql> SELECT * FROM Localisation LIMIT 10;
+-----------+-----------------------+-------------+
| communeID | commune               | departement |
+-----------+-----------------------+-------------+
|         0 | SAINT-ETIENNE-DU-BOIS |           1 |
|         1 | CHEVRY                |           1 |
|         2 | DIVONNE-LES-BAINS     |           1 |
|         3 | PERON                 |           1 |
|         4 | VALSERHONE            |           1 |
|         5 | CULOZ                 |           1 |
|         6 | ST-GENIS-POUILLY      |           1 |
|         7 | OYONNAX               |           1 |
|         8 | ST-GERMAIN-DE-JOUX    |           1 |
|         9 | GEX                   |           1 |
+-----------+-----------------------+-------------+

Here is my code to add the foreign key and the error output :

mysql> ALTER TABLE Caracteristique ADD CONSTRAINT fk_localisation FOREIGN KEY (communeID_id) REFERENCES Localisation(communeID);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`DATAImmo`.`#sql-4a4_9`, CONSTRAINT `fk_localisation` FOREIGN KEY (`communeID_id`) REFERENCES `Localisation` (`communeID`))

Maybe you can help, thanks

ahmedaao
  • 377
  • 1
  • 3
  • 14
  • Can you give me more details, please – ahmedaao Apr 25 '21 at 21:47
  • 5
    Use `SELECT * FROM caracteristique c WHERE NOT EXISTS (SELECT * FROM localisation l WHERE l.communeid = c.communeid_id);` to get the rows that violate the constraint. Then correct your data accordingly. – sticky bit Apr 25 '21 at 21:51
  • The current data is violating the foreign key you want to add, so it's rejected. Please clean up the data first, and then you can add the foreign key without any trouble. – The Impaler Apr 25 '21 at 22:28
  • @stickybit you should make than an answer – ysth Apr 25 '21 at 22:40

0 Answers0