I've got some problems understanding how FOREIGN KEY, REFERENCES and
CONSTRAINTS work.
I should not be able to insert data in a column pointed as FOREIGN KEY if
the data is not listed in the column referenced...am I right?

E.g.
- create table One (char character(3) primary key, num numeric(3) foreign
key references Two(x));
- create table two (y character(10) primary key, x numeric(3));

I'd expect to not be able to do something like this
- insert into One values("abc", 123);
if the value "123" is not in Two.x...but I can...what am I missing or
what am I looking for?

Thank everybody.

--
Nuno on zx-6r '04 working in progress...
666 the number of the beast, 70 the number of N1
*** www.gladio.org ***

On Mon, 05 Nov 2007 16:12:20 GMT, N1 <io@me.it> wrote:

>I've got some problems understanding how FOREIGN KEY, REFERENCES and
>CONSTRAINTS work.
>I should not be able to insert data in a column pointed as FOREIGN KEY if
>the data is not listed in the column referenced...am I right?
>
>E.g.
>- create table One (char character(3) primary key, num numeric(3) foreign
>key references Two(x));
>- create table two (y character(10) primary key, x numeric(3));
>
>I'd expect to not be able to do something like this
>- insert into One values("abc", 123);
>if the value "123" is not in Two.x...but I can...what am I missing or
>what am I looking for?
>
>Thank everybody.

Foreign key constraints only work in the InnoDB storage
engine, not MyISAM.
The syntax is accepted for tables with storage engine
MyISAM, but the constraint is not enforced.

HTH
--
( Kees
)
c[_] The amount of time between slipping on the peel and
landing on the pavement is precisely one bananosecond. (#71)