I'm new to MySQL but I have extensive experience with DB2 so I'm getting
quite confused about how MySQL is supposed to work.
I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am
trying to create a pair of InnoDB tables that are related to one
another via a foreign key. I created the tables successfully but when I try to
insert a row into the child table that violates the foreign key, MySQL
loads the bad row, even though the foreign key doesn't exist!
Here is the script I used to create and populate the tables:
--------------------------------------------------------------
use Sample;
drop table if exists dept;
create table dept( deptno char(3) not null, deptname varchar(36) not null, mgrno char(6), primary key(deptno) )Type=InnoDB; drop table if exists emp;
create table emp( empno char(6) not null, firstnme char(12) not null, midinit char(1), lastname char(15) not null, workdept char(3) not null, salary dec(9,2) not null, primary key(empno), index(workdept), foreign key(workdept) references dept(deptno) on delete restrict )Type=InnoDB; insert into dept values
('A00', 'Administration', '000010'), ('D11', 'Manufacturing', '000020'), ('E21', 'Education', '000030'); insert into emp values ('000010', 'Christine', 'I', 'Haas','A00',50000.00), ('000020', 'Cliff', ' ', 'Jones', 'D11', 30000.00), ('000030', 'FK', ' ', 'Mistake', 'X99', 12345.67), ('000040', 'Brad', ' ', 'Dean', 'E21', 35000.00); -------------------------------------------------------------------
I got a very big clue when I ran this command:
show table status from Sample;
It showed that my two tables were type "MyISAM", *not* "InnoDB". If my
tables really are "MyISAM", then I'm not surprised that the foreign key
constraint doesn't work since MyISAM doesn't support foreign keys, at least as I
understand the manual.
However, this doesn't answer the big question: *Why* aren't my tables
InnoDB since I explicitly defined them that way??
Can any MySQL veterans clear up this mystery for me?
Rhino --- rhino1 AT sympatico DOT ca "If you want the best seat in the house, you'll have to move the cat." |
- Re: newbie question Justin Scheiber
- RE: newbie question v7rg8
- Newbie Question Nicola Hartland
- Re: Newbie Question Antony Dovgal
- Ingres vs MySQL Igor Dorovskoy
- Re: Ingres vs MySQL Joel Rees
- re: Newbie Question Nicola Hartland
- Re: Newbie Question Antony Dovgal
- newbie question peter
- Re: newbie question Matt W
- Re: Newbie Question Rhino
- Re: Newbie Question Jeff Mathis
- Re: Newbie Question Jeff Mathis
- Re: Newbie Question Jeff Mathis
- Re: Newbie Question Jeff Mathis
- Re: Newbie Question Egor Egorov
- Re: Newbie Question Egor Egorov
- Re: Newbie Question Ed Leafe
- Re: Newbie Question Rhino
- Re: Newbie Question gerald_clark
- Re: Reply-to [was Newbie Questio... Duncan Hill