Hi Group,
I have two tables, emp and dept. The dept uses the MYISAM storage
engine and emp uses the INNODB one.

create table dept
(
deptno int primary key,
dept_name varchar(100)
) engine = MYISAM;


create table emp
(
empno int primary key,
ename varchar(100),
deptno int references dept(deptno)
)engine = INNODB;

Now when I try to create the emp table, I find that the foreign key
constraint doesnt get created. I know the fact that MYISAM tables dont
support constraints(correct me if I am wrong), which happens to be the
dept table in my case. But that should not prevent me creating the
referential constraint on my emp table.

If this doesnt work out, do I need to use triggers? Please suggest.


Thanks in advance
Jackal

jackal_on_work@yahoo.com wrote:
> Now when I try to create the emp table, I find that the foreign key
> constraint doesnt get created. I know the fact that MYISAM tables dont
> support constraints(correct me if I am wrong), which happens to be the
> dept table in my case. But that should not prevent me creating the
> referential constraint on my emp table.
Why shouldn't it? The referential constraint is operated between the tables,
if one of them doesn't support the constraint I would expect that fact to
prevent it.

On Sun, 04 Nov 2007 15:21:32 +0100, <jackal_on_work@yahoo.com> wrote:

> Hi Group,
> I have two tables, emp and dept. The dept uses the MYISAM storage
> engine and emp uses the INNODB one.
>
> create table dept
> (
> deptno int primary key,
> dept_name varchar(100)
> ) engine = MYISAM;
>
>
> create table emp
> (
> empno int primary key,
> ename varchar(100),
> deptno int references dept(deptno)
> )engine = INNODB;
>
> Now when I try to create the emp table, I find that the foreign key
> constraint doesnt get created. I know the fact that MYISAM tables dont
> support constraints(correct me if I am wrong), which happens to be the
> dept table in my case. But that should not prevent me creating the
> referential constraint on my emp table.

It does prevent it. Just make you dept table INNODB too.
--
Rik Wasmus

Thanks Rick, for the reply. But the challenge here is that the dept
table is being used for Full Text search.

On Nov 4, 9:54 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Sun, 04 Nov 2007 15:21:32 +0100, <jackal_on_w...@yahoo.com> wrote:
> > Hi Group,
> > I have two tables, emp and dept. The dept uses the MYISAM storage
> > engine and emp uses the INNODB one.
>
> > create table dept
> > (
> > deptno int primary key,
> > dept_name varchar(100)
> > ) engine = MYISAM;
>
> > create table emp
> > (
> > empno int primary key,
> > ename varchar(100),
> > deptno int references dept(deptno)
> > )engine = INNODB;
>
> > Now when I try to create the emp table, I find that the foreign key
> > constraint doesnt get created. I know the fact that MYISAM tables dont
> > support constraints(correct me if I am wrong), which happens to be the
> > dept table in my case. But that should not prevent me creating the
> > referential constraint on my emp table.
>
> It does prevent it. Just make you dept table INNODB too.
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

> Thanks Rick, for the reply. But the challenge here is that the dept
> table is being used for Full Text search.

In that case, welcome to MySQL where different storage engines
have different features :-)


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com