CODEWITHSUNDEEP
insertmariadbdefaultnotnull
SAM
SAM

Reputation: 31

How to Insert default into not null column if value is null in Maria db

I have below table structure:

CREATE TABLE Table_1(
    id int AutoIncrement PRIMARY KEY,
    message varchar(64) NOT NULL DEFAULT 'NA'
)

I run the below query:

INSERT INTO Table_1 (id, message) VALUES (null, null);

It gives me Error:

Error Code: 1048. Column 'message' cannot be null

I want the below result with the same insert query.

Output

|id | message|
|1  | NA     |

Are there any MySQL settings? Thanks

Upvotes: 3

Views: 6262

Answers (3)

alberto
alberto

Reputation: 33

The COALESCE function in combination with DEFAULT may be used to convert NULL values into whatever default has been defined for a table column. This is because COALESCE will return the first non-null element from its argument list. Although OP's INSERT would produce an error, the statement could be, conceptually, rewritten as:

INSERT INTO Table_1 (id, message) VALUES (null, COALESCE(null, DEFAULT(message)))

Not said by the OP, but if the statement is being generated by a function that receives message as argument:

function PutMessage(msg)
   Query("INSERT INTO Table_1 (id, message) VALUES (null,
      COALESCE(msg, DEFAULT(message)))")

In PHP, using mysqli and prepared statements:

// NOTE: Keep this configuration hidden somewhere else
$DbServer = "...";
$DbUser = "...";
$DbPass = "...";
$DbName = "...";

// Attempts to create a connection
$conn = new mysqli($DbServer, $DbUser, $DbPass, $DbName);
if ($conn->connect_error) ERROR

// Prepares an INSERT statement
$stmt = $conn->prepare("INSERT INTO Table_1 "
   "(id, message) VALUES (?, COALESCE(?, DEFAULT(message)))");
$stmt->bind_param("is", $id, $message);

// Inserts null message
$id = null;
$message = null;
$stmt->execute();

// Inserts a non null message
$id = null;
$message = "This is a non-null message";
$stmt->execute();

// Cleans everything up
$stmt->close();
$conn->close();

This also adds code injection prevention.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Your question is at first sight tricky, because conceptually you want to an insert without specifying a primary key value or a message, instead relying entirely on MySQL to provide the default values.

To insert an empty row, just specify the primary key column along with a single NULL for the VALUES:

CREATE TABLE Table_1(
    id int PRIMARY KEY AUTO_INCREMENT,
    message varchar(64) NOT NULL DEFAULT 'NA'
);

INSERT INTO Table_1 (id) VALUES (NULL);
SELECT * FROM Table_1;

Output:

   id | message
1  1  | NA

Demo here:

Rextester

Upvotes: 1

Noob
Noob

Reputation: 752

You had some syntax error this will work.

CREATE TABLE Table_1(
    id int NOT NULL AUTO_INCREMENT,
    message varchar(64) DEFAULT 'NA',
    PRIMARY KEY (id)
)

If you want to add value Null you should not add this constrains in your table stucture then.

Upvotes: 0

Related Questions