CODEWITHSUNDEEP
mysqlnode.jsexpressforeign-keyscreate-table
Ichrak Mansour
Ichrak Mansour

Reputation: 1942

Field 'foreign key' doesn't have a default value

I developed my backend using nodeJS and MySQL, which I have three tables as below :

Fournisseurs:

CREATE TABLE fournisseurs (

 Codef bigint(21) NOT NULL ,
 Noment varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 Prenomf varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (Codef, Prenomf)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Categorie :

CREATE TABLE categorie (

 Idcat  bigint(21) NOT NULL AUTO_INCREMENT,
 Nomcat varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (Idcat, Nomcat)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Produits :

CREATE TABLE produits (

 Codep bigint(21) NOT NULL AUTO_INCREMENT,
 Reference bigint(21) NOT NULL,
 Nomp varchar(20) COLLATE utf8_unicode_ci NOT NULL ,
 Codef bigint(21) NOT NULL  ,
 Prenomf varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 Idcat  bigint(21) NOT NULL  ,
 Nomcat varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 Description varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (Codep ),
 FOREIGN KEY (Codef, Prenomf) REFERENCES fournisseurs (Codef, Prenomf)
  ON DELETE CASCADE
 ON UPDATE CASCADE ,
 FOREIGN KEY (Idcat, Nomcat) REFERENCES categorie (Idcat, Nomcat)
 ON DELETE CASCADE
 ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10;

I try to insert into Produits table as you see below :

exports.ajouterprod = function(req, res) {
    console.log("req", req.body);
    var today = new Date();
    var produits = {
        "Reference": req.body.Reference,
        "Nomp": req.body.Nomp,
        // "Codef": req.body.Codef,
        "Prenomf": req.body.Prenomf,
        //"Idcat": req.body.Idcat,
        "Nomcat": req.body.Nomcat,
        "Description": req.body.Description
    }
    connection.query('INSERT INTO produits SET ?', produits, function(error, results, fields) {
        if (error) {
            console.log("error ocurred", error);
            res.send({
                "code": 400,
                "failed": "error ocurred"
            })
        }
        else {

            res.send({
                "code": 200,
                "success": "produit registered sucessfully"
            });
        }

    })
};

when I run it with Postman, I get : "failed": "error ocurred" and error ocurred { Error: ER_NO_DEFAULT_FOR_FIELD: Field 'Codef' doesn't have a default value on my backend. As you see Codef is a primary key on my table fournisseurs.

How can I fix that ?

Upvotes: 2

Views: 4601

Answers (1)

Shubham
Shubham

Reputation: 1288

Why this error:

Since you are inserting data to Produits and not specifying any value Codef, this error is generated as foreign key needs a value(can be null as well)

Solution 1: Alter table structure of Produits to have some default value that is either present in another table(where foreign key is referenced) or a null value.

Solution 2: Add some default at code level and same in table where foreign key is referenced.

Upvotes: 2

Related Questions