Thanks to the people who answer me in my previous thread, I can now rephrase
my problem :

I have a table A defined by :

create table A (
id bigint(9) NOT NULL ,
col bigint(9) NOT NULL ,
PRIMARY KEY (`id`)
)

if I insert a row with command : insert into a(id,col) values (1,null);
I get the correct exception :ERROR 1048: Column 'col' cannot be null

If I use the command : insert into a(id) values (1);
the row is create with value (1,0).

It seems to me that mysql is faulty for the second case. Do you agree with
me ?
What can I do for get rid of this default value and get an exception for
every command ?

Thanks in avance

Pierre

== Quote from Pierre Gilquin (inconnue@bluewin.ch)'s article
> Thanks to the people who answer me in my previous thread, I can now rephrase
> my problem :
> I have a table A defined by :
> create table A (
> id bigint(9) NOT NULL ,
> col bigint(9) NOT NULL ,
> PRIMARY KEY (`id`)
> )
> if I insert a row with command : insert into a(id,col) values (1,null);
> I get the correct exception :ERROR 1048: Column 'col' cannot be null
> If I use the command : insert into a(id) values (1);
> the row is create with value (1,0).
> It seems to me that mysql is faulty for the second case. Do you agree with
> me ?
> What can I do for get rid of this default value and get an exception for
> every command ?
> Thanks in avance
> Pierre

my suggestion is to create a trigger for this table for any insert and/or update
operations. good catch!

--
POST BY: lark with PHP News Reader ;o)

Pierre Gilquin wrote:

> create table A (
> id bigint(9) NOT NULL ,
> col bigint(9) NOT NULL ,
> PRIMARY KEY (`id`)
> )
> If I use the command : insert into a(id) values (1);
> the row is create with value (1,0).
> It seems to me that mysql is faulty for the second case. Do you agree with
> me ?

No, I can't agree with you. NULL != 0

NULL is "VOID", and ZERO is a value which is included in the INT and that
means you got a value all within the specification you gave when you created
the table.


> What can I do for get rid of this default value and get an exception for
> every command ?

Suggest you would use a function which then is used to insert the value to the
table.


--

//Aho

> Thanks to the people who answer me in my previous thread, I can now
rephrase
> my problem :
>
> I have a table A defined by :
>
> create table A (
> id bigint(9) NOT NULL ,
> col bigint(9) NOT NULL ,
> PRIMARY KEY (`id`)
> )
>
> if I insert a row with command : insert into a(id,col) values (1,null);
> I get the correct exception :ERROR 1048: Column 'col' cannot be null
>
> If I use the command : insert into a(id) values (1);
> the row is create with value (1,0).
>
> It seems to me that mysql is faulty for the second case. Do you agree with
> me ?

So, given that you don't have a DEFAULT clause, you're expecting
an exception? This is fair and one of the long annoying things with MySQL,
it just takes the "default" for the datatype when defined as NOT NULL.

> What can I do for get rid of this default value and get an exception for
> every command ?

You can get this exception if you use a different "sql mode", one that is
more strict. I believe MySQL 5 uses the more strict mode by default.

Check the documentation on sql mode, here's the reference for 5.1:
http://dev.mysql.com/doc/refman/5.1/...sql-modes.html

to get you an idea.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> > create table A (
> > id bigint(9) NOT NULL ,
> > col bigint(9) NOT NULL ,
> > PRIMARY KEY (`id`)
> > )
> > If I use the command : insert into a(id) values (1);
> > the row is create with value (1,0).
> > It seems to me that mysql is faulty for the second case. Do you agree
with
> > me ?
>
> No, I can't agree with you. NULL != 0
>
> NULL is "VOID", and ZERO is a value which is included in the INT and that
> means you got a value all within the specification you gave when you
created
> the table.

Yes, except that the OP did NOT insert a 0, but MySQL "assumed" a default
of 0 because the column is defined as "not null".


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thanks for you useful suggestion.

Pierre

"Martijn Tonies" <m.tonies@upscene.removethis.com> a écrit dans le message
de news: 476a4779$0$26876$e4fe514c@dreader27.news.xs4all.nl ...
>
>> Thanks to the people who answer me in my previous thread, I can now
> rephrase
>> my problem :
>>
>> I have a table A defined by :
>>
>> create table A (
>> id bigint(9) NOT NULL ,
>> col bigint(9) NOT NULL ,
>> PRIMARY KEY (`id`)
>> )
>>
>> if I insert a row with command : insert into a(id,col) values (1,null);
>> I get the correct exception :ERROR 1048: Column 'col' cannot be null
>>
>> If I use the command : insert into a(id) values (1);
>> the row is create with value (1,0).
>>
>> It seems to me that mysql is faulty for the second case. Do you agree
>> with
>> me ?
>
> So, given that you don't have a DEFAULT clause, you're expecting
> an exception? This is fair and one of the long annoying things with MySQL,
> it just takes the "default" for the datatype when defined as NOT NULL.
>
>> What can I do for get rid of this default value and get an exception for
>> every command ?
>
> You can get this exception if you use a different "sql mode", one that is
> more strict. I believe MySQL 5 uses the more strict mode by default.
>
> Check the documentation on sql mode, here's the reference for 5.1:
> http://dev.mysql.com/doc/refman/5.1/...sql-modes.html
>
> to get you an idea.
>
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>