SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 06:21 AM
| ||||
| ||||
 NOT NULL = Not Working? If I create the following table, and then try and insert the following data both inserts work. It looks like the second one works (it shouldn't because Last is NULL) because it assumes Last = ''. Is there a way I can make it NOT assume that? If Last is not specified it should reject that command. Is that possible? --------------------------------------------------------------- DROP TABLE IF EXISTS foo; CREATE TABLE foo ( ID INTEGER PRIMARY KEY AUTO_INCREMENT, First VarChar(30), Last VarChar(30) NOT NULL, Zip INTEGER ); INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013); INSERT INTO foo (Last) VALUES (17423);      |
|
02-28-2008, 06:21 AM
| |||
| |||
| Re: NOT NULL = Not Working? Scott Baker wrote: > If I create the following table, and then try and insert the following > data both inserts work. It looks like the second one works (it shouldn't > because Last is NULL) because it assumes Last = ''. Is there a way I can > make it NOT assume that? If Last is not specified it should reject that > command. Is that possible? > > --------------------------------------------------------------- > > DROP TABLE IF EXISTS foo; > > CREATE TABLE foo ( > ID INTEGER PRIMARY KEY AUTO_INCREMENT, > First VarChar(30), > Last VarChar(30) NOT NULL, > Zip INTEGER > ); > > INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013); > INSERT INTO foo (Last) VALUES (17423); > In your last insert example, Last is inserted as 17423. Which is not null. -- Thanks, James |
|
02-28-2008, 06:21 AM
| |||
| |||
| Re: NOT NULL = Not Working? Scott Baker escreveu: > If I create the following table, and then try and insert the following > data both inserts work. It looks like the second one works (it shouldn't > because Last is NULL) because it assumes Last = ''. Is there a way I can > make it NOT assume that? If Last is not specified it should reject that > command. Is that possible? > > --------------------------------------------------------------- > > DROP TABLE IF EXISTS foo; > > CREATE TABLE foo ( > ID INTEGER PRIMARY KEY AUTO_INCREMENT, > First VarChar(30), > Last VarChar(30) NOT NULL, > Zip INTEGER > ); > > INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013); > INSERT INTO foo (Last) VALUES (17423); > > I think that you are confuse because in your second command you're setting Last=17423 try this command INSERT INTO foo (Zip) VALUES (17423); and you'll see the message error saying that Last can't be NULL I hope that it helps -- Ricardo Conrado Serafim DBA Júnior (MySQL) URANET - www.uranet.com.br |
|
02-28-2008, 06:21 AM
| |||
| |||
| Re: NOT NULL = Not Working? JamesDR wrote: > Scott Baker wrote: >> If I create the following table, and then try and insert the following >> data both inserts work. It looks like the second one works (it shouldn't >> because Last is NULL) because it assumes Last = ''. Is there a way I can >> make it NOT assume that? If Last is not specified it should reject that >> command. Is that possible? >> >> --------------------------------------------------------------- >> >> DROP TABLE IF EXISTS foo; >> >> CREATE TABLE foo ( >> ID INTEGER PRIMARY KEY AUTO_INCREMENT, >> First VarChar(30), >> Last VarChar(30) NOT NULL, >> Zip INTEGER >> ); >> >> INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013); >> INSERT INTO foo (Last) VALUES (17423); >> > > In your last insert example, Last is inserted as 17423. Which is not null. > Yup, empty string, the manual says this... http://dev.mysql.com/doc/refman/5.0/...-defaults.html However it does say that to enforce NOT NULL you would have to change the sql_mode http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html EG: SET SESSION sql_mode='STRICT_ALL_TABLES'; INSERT INTO foo (zip) VALUES (12345); SET SESSION sql_mode=''; I get an error on the insert statement: "Field 'Last' doesn't have a default value." You may need to set sql_mode to STRICT_ALL_TABLES before the insert or do it in my.cnf or as a command line parameter. -- Thanks, James |
|
02-28-2008, 06:21 AM
| ||||
| ||||
| Re: NOT NULL = Not Working? I knew it was an option somewhere... Perfect! Thanks JamesDR wrote: > JamesDR wrote: >> Scott Baker wrote: >>> If I create the following table, and then try and insert the following >>> data both inserts work. It looks like the second one works (it shouldn't >>> because Last is NULL) because it assumes Last = ''. Is there a way I can >>> make it NOT assume that? If Last is not specified it should reject that >>> command. Is that possible? >>> >>> --------------------------------------------------------------- >>> >>> DROP TABLE IF EXISTS foo; >>> >>> CREATE TABLE foo ( >>> ID INTEGER PRIMARY KEY AUTO_INCREMENT, >>> First VarChar(30), >>> Last VarChar(30) NOT NULL, >>> Zip INTEGER >>> ); >>> >>> INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013); >>> INSERT INTO foo (Last) VALUES (17423); >>> >> In your last insert example, Last is inserted as 17423. Which is not null. >> > > Yup, empty string, the manual says this... > http://dev.mysql.com/doc/refman/5.0/...-defaults.html > > However it does say that to enforce NOT NULL you would have to change > the sql_mode > http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html > EG: > SET SESSION sql_mode='STRICT_ALL_TABLES'; > INSERT INTO foo (zip) VALUES (12345); > SET SESSION sql_mode=''; > > I get an error on the insert statement: > "Field 'Last' doesn't have a default value." > > You may need to set sql_mode to STRICT_ALL_TABLES before the insert or > do it in my.cnf or as a command line parameter. > > |
Linear Mode
