Thread: MySQLs' "FOREIGN KEYS¨

Rajesh Kumar Mallah wrote:

>
> MySQL does not thinks 0 to be a valid integer data .
> Although 0 is not present in master table
> it allows inserting in a referencing table
> as though 0 == NULL . This is really funny (dangerous)

What does it do if you have a NOT NULL constraint on the FK column and
insert 0?


Jan

>
>
> mysql> alter table slave add foreign key (id2) references master(id);
> Query OK, 6 rows affected (0.00 sec)
> Records: 6  Duplicates: 0  Warnings: 0
>
>
> mysql> select * from master;
> Empty set (0.00 sec)
>
> mysql>
>
>
> mysql> select * from slave;
> +------+
> | id2  |
> +------+
> |    0 |
> |    0 |
> |    0 |
> |    0 |
> |    0 |
> |    0 |
> +------+
> 6 rows in set (0.00 sec)


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Oops! really sorry , the tables were not of InnoDB type
that could have supported FKEYS. *Will post again* once
i get to put FKEYS properly on InnoDB tables.

Still in that case also the beast should tell loudly about
whats going on behind.

Mallah.

Jan Wieck wrote:

> Rajesh Kumar Mallah wrote:
>
>>
>> MySQL does not thinks 0 to be a valid integer data .
>> Although 0 is not present in master table
>> it allows inserting in a referencing table
>> as though 0 == NULL . This is really funny (dangerous)
>
>
> What does it do if you have a NOT NULL constraint on the FK column and
> insert 0?
>
>
> Jan
>
>>
>>
>> mysql> alter table slave add foreign key (id2) references master(id);
>> Query OK, 6 rows affected (0.00 sec)
>> Records: 6  Duplicates: 0  Warnings: 0
>>
>>
>> mysql> select * from master;
>> Empty set (0.00 sec)
>>
>> mysql>
>>
>>
>> mysql> select * from slave;
>> +------+
>> | id2  |
>> +------+
>> |    0 |
>> |    0 |
>> |    0 |
>> |    0 |
>> |    0 |
>> |    0 |
>> +------+
>> 6 rows in set (0.00 sec)
>
>
>

On Mon, 10 Nov 2003 08:42:13 +0530
Rajesh Kumar Mallah <mallah@trade-india.com> wrote:

>
> Oops! really sorry , the tables were not of InnoDB type
> that could have supported FKEYS. *Will post again* once
> i get to put FKEYS properly on InnoDB tables.
>
> Still in that case also the beast should tell loudly about
> whats going on behind.
>

Wow. You know, I'd really expect it to at least go
"Hey, you're using MyISAM tables, which don't actually do foriegn keys..
so.. heh. Hope you don't actually need them!"

instead of being led to believe (like what happened to you) that you
actually had FK's and started building an app that relies on that fact.


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

On Monday 10 Nov 2003 6:34 pm, Jeff wrote:
> On Mon, 10 Nov 2003 08:42:13 +0530
>
> Rajesh Kumar Mallah <mallah@trade-india.com> wrote:
> > Oops! really sorry , the tables were not of InnoDB type
> > that could have supported FKEYS. *Will post again* once
> > i get to put FKEYS properly on InnoDB tables.
> >
> > Still in that case also the beast should tell loudly about
> > whats going on behind.
>


> Wow. You know, I'd really expect it to at least go
> "Hey, you're using MyISAM tables, which don't actually do foriegn keys..
> so.. heh. Hope you don't actually need them!"
>
> instead of being led to believe (like what happened to you) that you
> actually had FK's and started building an app that relies on that fact.

The Mandrake RPMS' of mysql 4-0-14 does not create InnoDB tables
by default. In fact it does not even provide a /etc/my.cnf to tweak around.
I am not sure if their RPMS' (in mysqls' website) have default
settings of table handler to be InnoDB.In case default table handler is
MyISAM then its REALLY bad thing to hide and remain seilent abt.

Ok one thing that i think is good and MySQL enforces is INDEXES on
both the keys otherwise it gives  ERROR 1005. I think there was a old
thread on "Indexes on Foreign keys".

Now regarding the original problem, MySQL does not have that defect of
treating 0 as NULL , the confusion was becoz the tables were MyISAM that
did not support fkeys.

Again the error message that is given on FKEY violation is not as
good as ours  as in 7.4 , it does not indicate which key is violated
where ours does !

Regards
Mallah.

Guys,

> Ok one thing that i think is good and MySQL enforces is INDEXES on
> both the keys otherwise it gives  ERROR 1005. I think there was a old
> thread on "Indexes on Foreign keys".

FYI, the MySQL team (per Zak Greant) asked me to thank you for the great Q/C
you're doing on MySQL.   Seriously, he said this to me at PHPCon.

It's an odd thought, but maybe we should focus a little more on PostgreSQL and
let the MySQL people find their own bugs?

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 2003-11-10 at 13:04, Josh Berkus wrote:
> Guys,
>
> > Ok one thing that i think is good and MySQL enforces is INDEXES on
> > both the keys otherwise it gives  ERROR 1005. I think there was a old
> > thread on "Indexes on Foreign keys".
>
> FYI, the MySQL team (per Zak Greant) asked me to thank you for the great Q/C
> you're doing on MySQL.   Seriously, he said this to me at PHPCon.
>
> It's an odd thought, but maybe we should focus a little more on PostgreSQL and
> let the MySQL people find their own bugs?
>

Maybe they already tried looking for bugs in PostgreSQL and gave up ;-)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL