CODEWITHSUNDEEP
mysqlsql
Jenna
Jenna

Reputation: 11

Database creation with data insertion using mysql

Here is the error message I am getting, can you suggest any fixes?

Cannot insert the value NULL into column 'Quantity', table 'UOKA0209.dbo.SalesOrderLine'; column does not allow nulls. INSERT fails.

I am new to this and don't have much experience working in with this language.

This is what I have tried:

INSERT INTO [SalesOrderLine] ([SOLID],[Quantity],[SQLStatus])
VALUES
  (1896,default,default),
  (1789,default,default),
  (3789,default,default),
  (4789,default,default),
  (5789,default,default);

Upvotes: 1

Views: 39

Answers (1)

Zak
Zak

Reputation: 7515

You cannot use default if there is no default set in the database for null values. So instead you can change it to be nothing --> '' which is NOT NULL even though it's "empty" -- Or set a default for your NOT NULL field (Quantity) using ALTER

INSERT INTO UOKA0209.dbo.SalesOrderLine (`SOLID`, `Quantity`, `SQLStatus`) 
VALUES 
(1896,'',default),
(1789,'',default),
(3789,'',default),
(4789,'',default),
(5789,'',default);

Or alter your table:

 ALTER TABLE UOKA0209.dbo.SalesOrderLine 
     MODIFY Quantity INT NOT NULL DEFAULT 0;

And just an assumption -- You'll probably have the same problem with SQLStatus -- So change your query to: (4789,'',''), OR alter that column using the same alter command above, setting it to 0 by default .. MODIFY SQLStatus INT NOT NULL DEFAULT 0;

CONVERSELY -- If you did alter your table, and set the defaults for those two columns to 0 .. That makes your query much simpler:

INSERT INTO UOKA0209.dbo.SalesOrderLine (`SOLID`) 
VALUES 
(1896),
(1789),
(3789),
(4789),
(5789);

As Quantity and SQLStatus will now default to 0 without needing input from you to do so.

Upvotes: 1

Related Questions