2

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

SAM
  • 21
  • 1
  • 4

2 Answers2

1

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I want the 'message' column in the insert list like INSERT INTO Table_1 (id, message ) VALUES (NULL, NULL); – SAM Aug 22 '17 at 14:22
  • @SAM If you want to do this, then use my answer. If you have _other_ columns, then let us know what they are and the answer can be updated. – Tim Biegeleisen Aug 22 '17 at 15:05
0

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.

Noob
  • 732
  • 8
  • 30