MariaDB Connector/Node.js is used to connect applications developed on Node.js to MariaDB and MySQL databases. MariaDB Connector/Node.js is LGPL licensed.
GNU Lesser General Public License v2.1
368
stars
91
forks
source link
Error 'Cannot add or update a child row: a foreign key constraint fails' occurred only with mariadb nodejs package #160
CREATE TABLE IF NOT EXISTS files_typedoc (
id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
typename varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
extension varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
DateAjout timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (id),
UNIQUE KEY name (typename)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS files_uploaded (
id int(11) NOT NULL AUTO_INCREMENT,
filename char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'UUID v4',
StatusTime timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS contrats_documents (
id int(11) NOT NULL AUTO_INCREMENT,
IdEssai int(10) unsigned NOT NULL DEFAULT 0,
fk_files_typedoc tinyint(3) unsigned NOT NULL,
fk_files_uploaded int(11) NOT NULL,
StatusTime timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (id),
KEY contrats_documents_ibfk_1 (fk_files_uploaded),
KEY contrats_documents_ibfk_2 (fk_files_typedoc),
CONSTRAINT contrats_documents_ibfk_1 FOREIGN KEY (fk_files_uploaded) REFERENCES files_uploaded (id),
CONSTRAINT contrats_documents_ibfk_2 FOREIGN KEY (fk_files_typedoc) REFERENCES files_typedoc (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS contrats_documents_etats (
id int(11) NOT NULL AUTO_INCREMENT,
etat_nom varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
DateAjout timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS contrats_documents_validation (
id int(11) NOT NULL AUTO_INCREMENT,
contrats_documents_id int(11) NOT NULL,
contrats_documents_etats_id int(11) NOT NULL,
motif text COLLATE utf8mb4_unicode_ci NOT NULL,
StatusTime timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (id),
KEY fk_contrats_documents_etats_id (contrats_documents_etats_id),
KEY fk_contrats_documents_id (contrats_documents_id),
CONSTRAINT fk_contrats_documents_etats_id FOREIGN KEY (contrats_documents_etats_id) REFERENCES contrats_documents_etats (id),
CONSTRAINT fk_contrats_documents_id FOREIGN KEY (contrats_documents_id) REFERENCES contrats_documents (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
When I run this request (with right data in all others tables) :
INSERT INTO contrats_documents_validation (id, contrats_documents_id, contrats_documents_etats_id, motif, StatusTime) VALUES (NULL, 15, 3, 'TEST', current_timestamp())
In phpMyAdmin or HeidiSQL, no problem. The request adding a line correctly.
But the same request executed with mariadb NodeJS package is not working ! (see first picture)
Versions :
MariaDB database : 10.5.10
mariadb package : 2.5.3
nodejs : 15.14.0
HeidiSQL : 11.2.0.6213
phpMyAdmin : 5.1.0 on PHP 8.0.5
OS : Windows Server 2019 Datacenter v1809 build 17763.1971
closing, since this is an SQL question.
stackoverflow is more appropriate for that kind of question.
I imagine that you have that figure out since that time, but you cannot set NULL for a non null field
Hello guys, this error makes me crazy :
My database structure :
CREATE TABLE IF NOT EXISTS
files_typedoc
(id
tinyint(3) unsigned NOT NULL AUTO_INCREMENT,typename
varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,extension
varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,DateAjout
timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (id
), UNIQUE KEYname
(typename
) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;CREATE TABLE IF NOT EXISTS
files_uploaded
(id
int(11) NOT NULL AUTO_INCREMENT,filename
char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'UUID v4',StatusTime
timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (id
) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;CREATE TABLE IF NOT EXISTS
contrats_documents
(id
int(11) NOT NULL AUTO_INCREMENT,IdEssai
int(10) unsigned NOT NULL DEFAULT 0,fk_files_typedoc
tinyint(3) unsigned NOT NULL,fk_files_uploaded
int(11) NOT NULL,StatusTime
timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (id
), KEYcontrats_documents_ibfk_1
(fk_files_uploaded
), KEYcontrats_documents_ibfk_2
(fk_files_typedoc
), CONSTRAINTcontrats_documents_ibfk_1
FOREIGN KEY (fk_files_uploaded
) REFERENCESfiles_uploaded
(id
), CONSTRAINTcontrats_documents_ibfk_2
FOREIGN KEY (fk_files_typedoc
) REFERENCESfiles_typedoc
(id
) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;CREATE TABLE IF NOT EXISTS
contrats_documents_etats
(id
int(11) NOT NULL AUTO_INCREMENT,etat_nom
varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,DateAjout
timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (id
) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;CREATE TABLE IF NOT EXISTS
contrats_documents_validation
(id
int(11) NOT NULL AUTO_INCREMENT,contrats_documents_id
int(11) NOT NULL,contrats_documents_etats_id
int(11) NOT NULL,motif
text COLLATE utf8mb4_unicode_ci NOT NULL,StatusTime
timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (id
), KEYfk_contrats_documents_etats_id
(contrats_documents_etats_id
), KEYfk_contrats_documents_id
(contrats_documents_id
), CONSTRAINTfk_contrats_documents_etats_id
FOREIGN KEY (contrats_documents_etats_id
) REFERENCEScontrats_documents_etats
(id
), CONSTRAINTfk_contrats_documents_id
FOREIGN KEY (contrats_documents_id
) REFERENCEScontrats_documents
(id
) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;When I run this request (with right data in all others tables) :
INSERT INTO contrats_documents_validation (id, contrats_documents_id, contrats_documents_etats_id, motif, StatusTime) VALUES (NULL, 15, 3, 'TEST', current_timestamp())
In phpMyAdmin or HeidiSQL, no problem. The request adding a line correctly. But the same request executed with mariadb NodeJS package is not working ! (see first picture)
Versions : MariaDB database : 10.5.10 mariadb package : 2.5.3 nodejs : 15.14.0 HeidiSQL : 11.2.0.6213 phpMyAdmin : 5.1.0 on PHP 8.0.5 OS : Windows Server 2019 Datacenter v1809 build 17763.1971
Thanks for helping me :)