value by default 0

Hi all,


I have create a table A with a foreign key for a relation to table B.
The relation must be benot null so the corresponding attribute is set as
not null.

If I create a new A with the foreign key null, the record is saved with a 0.
I would expect that MySql would send me an exception without saving.
How can I get the correct behaviour ?

Thanks in advance for any help.


Pierre

MySQl 4.0.18

On Wed, 19 Dec 2007 14:12:31 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:

> Hi all,
>
>
> I have create a table A with a foreign key for a relation to table B.
> The relation must be benot null so the corresponding attribute is set as
> not null.
>
> If I create a new A with the foreign key null, the record is saved with
> a 0.
> I would expect that MySql would send me an exception without saving.
> How can I get the correct behaviour ?

It does limit the insert here.
Can you show use the CREATE TABLE syntax for both tables? (and does a key
with 0 exist in B, just to be sure?)
--
Rik Wasmus

Thanks Rik,

It's something like that :

CREATE TABLE A( ID BIGINT(9) NOT NULL, linkOnTableBId BIGINT(9) NOT NULL,
.... );

ALTER TABLE interaction ADD PRIMARY KEY (ID);


This is very simple as it is generated by a Object - Relationnal Modelling
tools. The constraint for the relation is not managed in the database.
There is no attempt to create a B with id = 0.

The problem is that the default value for linkOnTableBId is 0 and I cannot
get rid of this.


Pierre






"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k3ll1z5bnjuv@metallium.lan...
> On Wed, 19 Dec 2007 14:12:31 +0100, Pierre Gilquin <inconnue@bluewin.ch>
> wrote:
>
>> Hi all,
>>
>>
>> I have create a table A with a foreign key for a relation to table B.
>> The relation must be benot null so the corresponding attribute is set as
>> not null.
>>
>> If I create a new A with the foreign key null, the record is saved with
>> a 0.
>> I would expect that MySql would send me an exception without saving.
>> How can I get the correct behaviour ?
>
> It does limit the insert here.
> Can you show use the CREATE TABLE syntax for both tables? (and does a key
> with 0 exist in B, just to be sure?)
> --
> Rik Wasmus

On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:
> It's something like that :

'something like' is not what I'm looking for. Could you post the full
definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
to see the definitions and pinpoint it directly instead of trying to list
all possible reasons why this could/should fail.

> ALTER TABLE interaction ADD PRIMARY KEY (ID);

This is never in a CREATE TABLE syntax.
--
Rik Wasmus

This is the real one :

The main table is Interaction

SHOW CREATE TABLE interaction
CREATE TABLE `interaction` (
`audit_id` mediumint(9) default NULL,
`category` char(3) default NULL,
`ID` bigint(9) NOT NULL default '0',
`impact` char(1) default NULL,
`substance1Id` bigint(9) NOT NULL default '0',
`substance2Id` bigint(9) NOT NULL default '0',
`type` char(1) default NULL,
`pourcent` decimal(5,2) default NULL,
`Ki` decimal(15,5) default NULL,
`Km` decimal(15,5) default NULL,
`heriteAtcFromSubstance` varchar(5) default 'true',
`infoImportId` bigint(9) default NULL,
`impactValue` decimal(15,5) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `SUB1_SUB2_IDX`
(`substance1Id`,`substance2Id`,`category`,`type`)
) TYPE=MyISAM

You can see the 2 attributes used for a relation on table Substance

( `substance1Id` bigint(9) NOT NULL default '0',
`substance2Id` bigint(9) NOT NULL default '0',)

if at least one of theses attributes is null, I would like an exception ...

CREATE TABLE `substance` (
`AUC` decimal(15,5) default NULL,
`Cmax` decimal(15,5) default NULL,
`DCI` blob,
`Q0` decimal(15,5) default NULL,
`TDemi` decimal(15,5) default NULL,
`Vd` decimal(15,5) default NULL,
`id` mediumint(9) NOT NULL default '0',
`biodisponibilite` decimal(15,5) default NULL,
`category` char(3) NOT NULL default '',
`idEMediat` mediumint(9) default NULL,
`invisible` tinyint(1) default NULL,
`isomere` char(3) default NULL,
`VERROU` tinyint(1) default NULL,
`metabolite` tinyint(1) default NULL,
`metaboliteActif` tinyint(1) default NULL,
`nomLocal` varchar(255) NOT NULL default '',
`poidsMoleculaire` decimal(15,5) default NULL,
`fu` decimal(15,5) default NULL,
`ka` decimal(15,5) default NULL,
`cl` decimal(15,5) default NULL,
`CmaxSs` decimal(15,5) default NULL,
`tauRefCmaxSs` decimal(15,5) default NULL,
`doseRefCmax` decimal(15,5) default NULL,
`tempsRefAUC` decimal(15,5) default NULL,
PRIMARY KEY (`id`),
KEY `nomSubstance` (`nomLocal`)
) TYPE=MyISAM

Pierre




"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k4ca0j5bnjuv@metallium.lan...
> On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <inconnue@bluewin.ch>
> wrote:
>> It's something like that :
>
> 'something like' is not what I'm looking for. Could you post the full
> definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
> to see the definitions and pinpoint it directly instead of trying to list
> all possible reasons why this could/should fail.
>
>> ALTER TABLE interaction ADD PRIMARY KEY (ID);
>
> This is never in a CREATE TABLE syntax.
> --
> Rik Wasmus

You are right, I tryed a more simple :

CREATE TABLE A( ID BIGINT(9) NOT NULL, linkOnTableBId BIGINT(9) NOT NULL);

ALTER TABLE A ADD PRIMARY KEY (ID);

and in this case, I cannot save with a null value for linkOnTableBId !
The problem is on the complete definition of my table

Pierre

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k4ca0j5bnjuv@metallium.lan...
> On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <inconnue@bluewin.ch>
> wrote:
>> It's something like that :
>
> 'something like' is not what I'm looking for. Could you post the full
> definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
> to see the definitions and pinpoint it directly instead of trying to list
> all possible reasons why this could/should fail.
>
>> ALTER TABLE interaction ADD PRIMARY KEY (ID);
>
> This is never in a CREATE TABLE syntax.
> --
> Rik Wasmus

On 19 Dec, 13:53, "Pierre Gilquin" <incon...@bluewin.ch> wrote:
> This is the real one :
>
> The main table is Interaction
>
> SHOW CREATE TABLE interaction
> CREATE TABLE `interaction` (
> `audit_id` mediumint(9) default NULL,
> `category` char(3) default NULL,
> `ID` bigint(9) NOT NULL default '0',
> `impact` char(1) default NULL,
> `substance1Id` bigint(9) NOT NULL default '0',
> `substance2Id` bigint(9) NOT NULL default '0',
> `type` char(1) default NULL,
> `pourcent` decimal(5,2) default NULL,
> `Ki` decimal(15,5) default NULL,
> `Km` decimal(15,5) default NULL,
> `heriteAtcFromSubstance` varchar(5) default 'true',
> `infoImportId` bigint(9) default NULL,
> `impactValue` decimal(15,5) default NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `SUB1_SUB2_IDX`
> (`substance1Id`,`substance2Id`,`category`,`type`)
> ) TYPE=MyISAM
>
> You can see the 2 attributes used for a relation on table Substance
>
> ( `substance1Id` bigint(9) NOT NULL default '0',
> `substance2Id` bigint(9) NOT NULL default '0',)
>
> if at least one of theses attributes is null, I would like an exception ....
>
> CREATE TABLE `substance` (
> `AUC` decimal(15,5) default NULL,
> `Cmax` decimal(15,5) default NULL,
> `DCI` blob,
> `Q0` decimal(15,5) default NULL,
> `TDemi` decimal(15,5) default NULL,
> `Vd` decimal(15,5) default NULL,
> `id` mediumint(9) NOT NULL default '0',
> `biodisponibilite` decimal(15,5) default NULL,
> `category` char(3) NOT NULL default '',
> `idEMediat` mediumint(9) default NULL,
> `invisible` tinyint(1) default NULL,
> `isomere` char(3) default NULL,
> `VERROU` tinyint(1) default NULL,
> `metabolite` tinyint(1) default NULL,
> `metaboliteActif` tinyint(1) default NULL,
> `nomLocal` varchar(255) NOT NULL default '',
> `poidsMoleculaire` decimal(15,5) default NULL,
> `fu` decimal(15,5) default NULL,
> `ka` decimal(15,5) default NULL,
> `cl` decimal(15,5) default NULL,
> `CmaxSs` decimal(15,5) default NULL,
> `tauRefCmaxSs` decimal(15,5) default NULL,
> `doseRefCmax` decimal(15,5) default NULL,
> `tempsRefAUC` decimal(15,5) default NULL,
> PRIMARY KEY (`id`),
> KEY `nomSubstance` (`nomLocal`)
> ) TYPE=MyISAM
>
> Pierre
>
> "Rik Wasmus" <luiheidsgoe...@hotmail.com> a écrit dans le message de news:
> op.t3k4ca0j5bn...@metallium.lan...
>
> > On Wed, 19 Dec 2007 14:38:34 +0100, Pierre Gilquin <incon...@bluewin.ch>
> > wrote:
> >> It's something like that :
>
> > 'something like' is not what I'm looking for. Could you post the full
> > definitions? Just run SHOW CREATE TABLE <tablename>. It really saves time
> > to see the definitions and pinpoint it directly instead of trying to list
> > all possible reasons why this could/should fail.
>
> >> ALTER TABLE interaction ADD PRIMARY KEY (ID);
>
> > This is never in a CREATE TABLE syntax.
> > --
> > Rik Wasmus

You have defined the fields with a default value of 0. So if the
fields are not supplied for the insert or the supplied values are
NULL, the default will be used.

On Wed, 19 Dec 2007 14:53:35 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:

> This is the real one :
>
> The main table is Interaction
>
> SHOW CREATE TABLE interaction
> CREATE TABLE `interaction` (
> `audit_id` mediumint(9) default NULL,
........
> `impactValue` decimal(15,5) default NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `SUB1_SUB2_IDX`
> (`substance1Id`,`substance2Id`,`category`,`type`)
> ) TYPE=MyISAM
>
> CREATE TABLE `substance` (
> `AUC` decimal(15,5) default NULL,
..........
> `tempsRefAUC` decimal(15,5) default NULL,
> PRIMARY KEY (`id`),
> KEY `nomSubstance` (`nomLocal`)
> ) TYPE=MyISAM

You have no foreign key constraints (what makes you think you do?), and
further more foreign key constraints are not supported in the MyISAM
engine, you'll probably want to convert them to InnoDB.
--
Rik Wasmus

It's seems that it's not the problem. I test with a very simple table

CREATE TABLE `a` (
`ID` bigint(9) NOT NULL default '0',
`linkOnTableBId` bigint(9) NOT NULL default '0',
PRIMARY KEY (`ID`)
) TYPE=MyISAM
then I try to insert :
> insert into a values (1,null)
This give the correct exception : ERROR 1048: Column 'linkOnTableBId' cannot
be null

I still dont know where is the problem is my real table

Thanks

"Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message de
news: 646c25a2-8480-47ed-b331-239e86ba62ac...oglegroups.com...
On 19 Dec, 13:53, "Pierre Gilquin" <incon...@bluewin.ch> wrote:


You have defined the fields with a default value of 0. So if the
fields are not supplied for the insert or the supplied values are
NULL, the default will be used.

Yes, there is no foreign key constraints in the database. My
Object-Relationnal mapping take care of this.
So for MySql, it's just a attribute with not null constraint.

But if I insert like this :
insert into interaction (ID) values (99999999)
The interaction is created and substance1Id substance2Id are both 0 !

If I test with a simple test table, I get the correct exception (please read
my reply to Captain Paralityc)

Can the problem be from the unique key ?

Pierre

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de news:
op.t3k5d1155bnjuv@metallium.lan...
On Wed, 19 Dec 2007 14:53:35 +0100, Pierre Gilquin <inconnue@bluewin.ch>
wrote:

You have no foreign key constraints (what makes you think you do?), and
further more foreign key constraints are not supported in the MyISAM
engine, you'll probably want to convert them to InnoDB.
--
Rik Wasmus