ok, more specific: here is a table:
CREATE TABLE FOO (foo1 int NOT NULL, foo2 int NOT NULL Default 1) If I do an insert into the FOO table with the following: INSERT INTO FOO (foo1,foo2) VALUES (1,1) Everything works as planned. If I do an insert into the FOO table with the following: INSERT INTO FOO (foo1) VALUES (1) Everything works as planned If I do an insert into the FOO table with the following: INSERT INTO FOO (foo2) VALUES (1) I want an error. But what happens is that the db engine explicitly defines a default of 0 to the foo1 column even though I defined it as int NOT NULL and did not give it a default. After I change the configuration with the following: CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure and re-create my table in a new database, I do get an error on INSERT INTO FOO (foo2) VALUES (1) because I didn't give a value for foo1, but I also get an error on this: INSERT INTO FOO (foo1) VALUES (1) even though I have explicitly defined a default for foo2. So..... What I want: No default unless I define one Error on NOT NULL if I don't define a default Default inserted if NOT NULL and I define a default and I don't specify a value in an insert statement Does this make more sense? Thanks, David ----- Original Message ----- From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, March 13, 2002 6:54 AM Subject: Re: Changing Default for Not Null to NOTHING > David, > Friday, March 08, 2002, 11:03:30 PM, you wrote: > > DMP> I just tried something and would a second opinion: > > DMP> I re-configured my MySQL server on my RH Linux machine using the following : > > DMP> CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure > > DMP> and ran the following SQL: > > DMP> CREATE TABLE FOO (foo int NOT NULL DEFAULT 5, foo1 INT NULL); > > DMP> INSERT INTO FOO (foo1) VALUES (1); > > DMP> MySQL-Error: > DMP> ====================== > DMP> 1048 - Column 'foo' cannot be null. > > DMP> So is it true that you either have an installation of MySQL that will have > DMP> defaults for NOT NULL defined columns (explicity or implied) or you can have > DMP> an installation that does not allow default values for NOT NULL defined > DMP> columns? > > Yes, if you specify CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure > you will have errors in INSERT statements for NOT NULL columns > inserting NULL values. > > DMP> What I want is to be able to create a table that has columns defined as NOT > DMP> NULL and if I don't assign a default to that column, I want an error if I > DMP> don't give it a value on insert. What I DON'T want is to define a column as > DMP> NOT NULL with NO EXPLICIT DEFAULT to get a value if it is left out of an > DMP> insert statements list of column/value set. > DMP> Does this make sense?? > > Looks like it does but try to be more clear. > > > -- > For technical support contracts, goto https://order.mysql.com/ > This email is sponsored by Ensita.net http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > <___/ www.mysql.com > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
