I have a MariaDB database in conjuction with an Express.js backend and therefore use the MariaDB provided Node.js Connector. I Initialized a Database with a Table that looks like this:
CREATE TABLE IF NOT EXISTS `Threads` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `title` TINYTEXT NOT NULL, `post` TEXT NOT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (`id`) );
Using the Node.js-Connector in my Backend, I want to insert some data to this table and use the default values for "created_at" and "updated_at".
I was thinking of something like this:
const insertThreadQuery = 'INSERT INTO Threads VALUES (?, ?, ?, ?, ?)'; con = await pool.getConnection(); const result = await con.query(insertThreadQuery, [null, "title", "post", null, null]);
Which obivously throws an error that tells me, that I cannot insert null for these Values
((conn=4, no: 1048, SQLState: 23000) Column 'updated_at' cannot be null sql: INSERT INTO Threads VALUES (?, ?, ?, ?, ?) - parameters:[null,'title','post',null,null])
My question is: How can I insert an entry like I was showing before, but instead of inserting "null" insert something else so my columns created_at and updated_at use the default value?
If you want to use DEFAULT values, then pass DEFAULT instead of NULL.
const insertThreadQuery = 'INSERT INTO Threads VALUES (NULL, ?, ?, DEFAULT, DEFAULT)'; con = await pool.getConnection(); const result = await con.query(insertThreadQuery, ["title", "post"]);
Answered By – Georg Richter