0

I have created a table with some attributes that have the NOT NULL constraint, then I have tried a INSERT INTO instruction, specifying values only for the fields that don't have the NOT NULL constraint, but the instruction still works. Shouldn't it work and give an error?

CREATE TABLE ciao(
  Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Nome VARCHAR(30) NOT NULL,
  Cognome VARCHAR(30) NOT NULL,
  Nickname VARCHAR(30)
); 

INSERT INTO ciao(Nickname) VALUES ('prova');
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3603880
  • 1
  • 5
  • 5
    do you have default values for those NOT NULL columns ? – ogres May 20 '14 at 15:25
  • 2
    Show us how you created the table (or it's schema) and how you add records. – mareckmareck May 20 '14 at 15:31
  • CREATE TABLE ciao( Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Nome VARCHAR(30) NOT NULL, Cognome VARCHAR(30) NOT NULL, Nickname VARCHAR(30)); insert instruction: INSERT INTO ciao(Nickname) VALUES ('prova'); – user3603880 May 20 '14 at 16:01
  • Your statements fail when trying them in sqlfiddle – Lamak May 20 '14 at 16:10
  • What value is it adding to the Nome & Cognome fields? Are you doing the insert directly to the DB (e.g., through phpmyadmin) or through an application (e.g., PHP code)? – Dave Jemison May 20 '14 at 16:11
  • through phpymadmin, and when it is executed, I don't see null values in the skipped fields, but the fields are blank. – user3603880 May 20 '14 at 16:17

1 Answers1

0

It is inserting empty string as the default value for the columns you didn't supply. That default is not specified in your create statement, so it's probably been created by someone else. If you run your create and insert on a clean DB, it fails.

Jasmine
  • 4,003
  • 2
  • 29
  • 39
  • I think it only fails if you enable "strict mode". The default installation will silently supply some default values for not null columns that are not specified in the inser. –  May 20 '14 at 16:27
  • Ok thank you for your clarification. Probably that's the matter. – user3603880 May 20 '14 at 16:29
  • Well, the ANSI mode and the Traditional mode should fail as well, but I can't try it ATM. – Jasmine May 20 '14 at 16:33