Foreign Key Problem

Foreign Key Problem

am 18.10.2006 19:38:20 von coosa

Dear all,

I'm new under mysql and have installed mysql5.0.24a community edition
for win32.
I have tried to implement a foreign key for this following sample
scenario:

CREATE TABLE student (
student_id INTEGER NOT NULL AUTO_INCREMENT,
student_name VARCHAR(100) NOT NULL,
CONSTRAINT PK_student PRIMARY KEY (student_id)
) ENGINE=INNODB;


CREATE TABLE faculty (
faculty_id INTEGER NOT NULL AUTO_INCREMENT,
faculty_name VARCHAR(100) NOT NULL,
CONSTRAINT PK_faculty PRIMARY KEY (faculty_id)
) ENGINE=INNODB;

CREATE TABLE student_faculty (
student_id INTEGER NOT NULL,
faculty_id INTEGER NOT NULL,
CONSTRAINT PK_student_faculty PRIMARY KEY (student_id, faculty_id)
) ENGINE=INNODB;

ALTER TABLE student_faculty ADD CONSTRAINT student_student_faculty
FOREIGN KEY (student_id) REFERENCES student (student_id);

ALTER TABLE student_faculty ADD CONSTRAINT faculty_student_faculty
FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id);

insert into student (student_name) values ('John');
insert into student (student_name) values ('Robert');

insert into faculty (faculty_name) values ('Information Technology');
insert into faculty (faculty_name) values ('Engineering');

Now there are student_id 1 and 2; same goes for faculty_id
so this statement should be ok:
insert into student_faculty (student_id, faculty_id) values (1,1);
However, there is no student_id or faculty_id greater than 2 yet, but
this statement still executes from mysql:
insert into student_faculty (student_id, faculty_id) values (3,1);
whereby it shouldn't since it does not inforce integrity based on the
foreign keys i have created!

Re: Foreign Key Problem

am 18.10.2006 23:12:41 von coosa

Ok, i have checked back again and it seems the my storage engine is not
Innodb but ISAM!
Is there a way i can change the engine into Innodb?
I have the default MySql Administrator from mysql.com and phpmyadmin
and none of them offer a a way to change that


coosa wrote:
> Dear all,
>
> I'm new under mysql and have installed mysql5.0.24a community edition
> for win32.
> I have tried to implement a foreign key for this following sample
> scenario:
>
> CREATE TABLE student (
> student_id INTEGER NOT NULL AUTO_INCREMENT,
> student_name VARCHAR(100) NOT NULL,
> CONSTRAINT PK_student PRIMARY KEY (student_id)
> ) ENGINE=INNODB;
>
>
> CREATE TABLE faculty (
> faculty_id INTEGER NOT NULL AUTO_INCREMENT,
> faculty_name VARCHAR(100) NOT NULL,
> CONSTRAINT PK_faculty PRIMARY KEY (faculty_id)
> ) ENGINE=INNODB;
>
> CREATE TABLE student_faculty (
> student_id INTEGER NOT NULL,
> faculty_id INTEGER NOT NULL,
> CONSTRAINT PK_student_faculty PRIMARY KEY (student_id, faculty_id)
> ) ENGINE=INNODB;
>
> ALTER TABLE student_faculty ADD CONSTRAINT student_student_faculty
> FOREIGN KEY (student_id) REFERENCES student (student_id);
>
> ALTER TABLE student_faculty ADD CONSTRAINT faculty_student_faculty
> FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id);
>
> insert into student (student_name) values ('John');
> insert into student (student_name) values ('Robert');
>
> insert into faculty (faculty_name) values ('Information Technology');
> insert into faculty (faculty_name) values ('Engineering');
>
> Now there are student_id 1 and 2; same goes for faculty_id
> so this statement should be ok:
> insert into student_faculty (student_id, faculty_id) values (1,1);
> However, there is no student_id or faculty_id greater than 2 yet, but
> this statement still executes from mysql:
> insert into student_faculty (student_id, faculty_id) values (3,1);
> whereby it shouldn't since it does not inforce integrity based on the
> foreign keys i have created!

Re: Foreign Key Problem

am 19.10.2006 07:39:48 von peterloh

First of all, issue the SHOW ENGINES query to determine if you have
InnoDB.

If not, the easiest way is probably to reinstall MySQL, making sure to
do a detailed installation and selecting InnoDB.

coosa wrote:
> Ok, i have checked back again and it seems the my storage engine is not
> Innodb but ISAM!
> Is there a way i can change the engine into Innodb?
> I have the default MySql Administrator from mysql.com and phpmyadmin
> and none of them offer a a way to change that
>
>
> coosa wrote:
> > Dear all,
> >
> > I'm new under mysql and have installed mysql5.0.24a community edition
> > for win32.
> > I have tried to implement a foreign key for this following sample
> > scenario:
> >
> > CREATE TABLE student (
> > student_id INTEGER NOT NULL AUTO_INCREMENT,
> > student_name VARCHAR(100) NOT NULL,
> > CONSTRAINT PK_student PRIMARY KEY (student_id)
> > ) ENGINE=INNODB;
> >
> >
> > CREATE TABLE faculty (
> > faculty_id INTEGER NOT NULL AUTO_INCREMENT,
> > faculty_name VARCHAR(100) NOT NULL,
> > CONSTRAINT PK_faculty PRIMARY KEY (faculty_id)
> > ) ENGINE=INNODB;
> >
> > CREATE TABLE student_faculty (
> > student_id INTEGER NOT NULL,
> > faculty_id INTEGER NOT NULL,
> > CONSTRAINT PK_student_faculty PRIMARY KEY (student_id, faculty_id)
> > ) ENGINE=INNODB;
> >
> > ALTER TABLE student_faculty ADD CONSTRAINT student_student_faculty
> > FOREIGN KEY (student_id) REFERENCES student (student_id);
> >
> > ALTER TABLE student_faculty ADD CONSTRAINT faculty_student_faculty
> > FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id);
> >
> > insert into student (student_name) values ('John');
> > insert into student (student_name) values ('Robert');
> >
> > insert into faculty (faculty_name) values ('Information Technology');
> > insert into faculty (faculty_name) values ('Engineering');
> >
> > Now there are student_id 1 and 2; same goes for faculty_id
> > so this statement should be ok:
> > insert into student_faculty (student_id, faculty_id) values (1,1);
> > However, there is no student_id or faculty_id greater than 2 yet, but
> > this statement still executes from mysql:
> > insert into student_faculty (student_id, faculty_id) values (3,1);
> > whereby it shouldn't since it does not inforce integrity based on the
> > foreign keys i have created!

Re: Foreign Key Problem

am 19.10.2006 11:50:02 von coosa

Is there a way to plaay around with my.ini file instead of
reinstalling?

Peter wrote:
> First of all, issue the SHOW ENGINES query to determine if you have
> InnoDB.
>
> If not, the easiest way is probably to reinstall MySQL, making sure to
> do a detailed installation and selecting InnoDB.
>
> coosa wrote:
> > Ok, i have checked back again and it seems the my storage engine is not
> > Innodb but ISAM!
> > Is there a way i can change the engine into Innodb?
> > I have the default MySql Administrator from mysql.com and phpmyadmin
> > and none of them offer a a way to change that
> >
> >
> > coosa wrote:
> > > Dear all,
> > >
> > > I'm new under mysql and have installed mysql5.0.24a community edition
> > > for win32.
> > > I have tried to implement a foreign key for this following sample
> > > scenario:
> > >
> > > CREATE TABLE student (
> > > student_id INTEGER NOT NULL AUTO_INCREMENT,
> > > student_name VARCHAR(100) NOT NULL,
> > > CONSTRAINT PK_student PRIMARY KEY (student_id)
> > > ) ENGINE=INNODB;
> > >
> > >
> > > CREATE TABLE faculty (
> > > faculty_id INTEGER NOT NULL AUTO_INCREMENT,
> > > faculty_name VARCHAR(100) NOT NULL,
> > > CONSTRAINT PK_faculty PRIMARY KEY (faculty_id)
> > > ) ENGINE=INNODB;
> > >
> > > CREATE TABLE student_faculty (
> > > student_id INTEGER NOT NULL,
> > > faculty_id INTEGER NOT NULL,
> > > CONSTRAINT PK_student_faculty PRIMARY KEY (student_id, faculty_id)
> > > ) ENGINE=INNODB;
> > >
> > > ALTER TABLE student_faculty ADD CONSTRAINT student_student_faculty
> > > FOREIGN KEY (student_id) REFERENCES student (student_id);
> > >
> > > ALTER TABLE student_faculty ADD CONSTRAINT faculty_student_faculty
> > > FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id);
> > >
> > > insert into student (student_name) values ('John');
> > > insert into student (student_name) values ('Robert');
> > >
> > > insert into faculty (faculty_name) values ('Information Technology');
> > > insert into faculty (faculty_name) values ('Engineering');
> > >
> > > Now there are student_id 1 and 2; same goes for faculty_id
> > > so this statement should be ok:
> > > insert into student_faculty (student_id, faculty_id) values (1,1);
> > > However, there is no student_id or faculty_id greater than 2 yet, but
> > > this statement still executes from mysql:
> > > insert into student_faculty (student_id, faculty_id) values (3,1);
> > > whereby it shouldn't since it does not inforce integrity based on the
> > > foreign keys i have created!

Re: Foreign Key Problem

am 20.10.2006 03:14:21 von peterloh

You could try using the C:\Program Files\MySQL\MySQL Server
5.0\bin\MySQLInstanceConfig.exe tool to enable InnoDB.

coosa wrote:
> Is there a way to plaay around with my.ini file instead of
> reinstalling?
>
> Peter wrote:
> > First of all, issue the SHOW ENGINES query to determine if you have
> > InnoDB.
> >
> > If not, the easiest way is probably to reinstall MySQL, making sure to
> > do a detailed installation and selecting InnoDB.
> >
> > coosa wrote:
> > > Ok, i have checked back again and it seems the my storage engine is not
> > > Innodb but ISAM!
> > > Is there a way i can change the engine into Innodb?
> > > I have the default MySql Administrator from mysql.com and phpmyadmin
> > > and none of them offer a a way to change that
> > >
> > >
> > > coosa wrote:
> > > > Dear all,
> > > >
> > > > I'm new under mysql and have installed mysql5.0.24a community edition
> > > > for win32.
> > > > I have tried to implement a foreign key for this following sample
> > > > scenario:
> > > >
> > > > CREATE TABLE student (
> > > > student_id INTEGER NOT NULL AUTO_INCREMENT,
> > > > student_name VARCHAR(100) NOT NULL,
> > > > CONSTRAINT PK_student PRIMARY KEY (student_id)
> > > > ) ENGINE=INNODB;
> > > >
> > > >
> > > > CREATE TABLE faculty (
> > > > faculty_id INTEGER NOT NULL AUTO_INCREMENT,
> > > > faculty_name VARCHAR(100) NOT NULL,
> > > > CONSTRAINT PK_faculty PRIMARY KEY (faculty_id)
> > > > ) ENGINE=INNODB;
> > > >
> > > > CREATE TABLE student_faculty (
> > > > student_id INTEGER NOT NULL,
> > > > faculty_id INTEGER NOT NULL,
> > > > CONSTRAINT PK_student_faculty PRIMARY KEY (student_id, faculty_id)
> > > > ) ENGINE=INNODB;
> > > >
> > > > ALTER TABLE student_faculty ADD CONSTRAINT student_student_faculty
> > > > FOREIGN KEY (student_id) REFERENCES student (student_id);
> > > >
> > > > ALTER TABLE student_faculty ADD CONSTRAINT faculty_student_faculty
> > > > FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id);
> > > >
> > > > insert into student (student_name) values ('John');
> > > > insert into student (student_name) values ('Robert');
> > > >
> > > > insert into faculty (faculty_name) values ('Information Technology');
> > > > insert into faculty (faculty_name) values ('Engineering');
> > > >
> > > > Now there are student_id 1 and 2; same goes for faculty_id
> > > > so this statement should be ok:
> > > > insert into student_faculty (student_id, faculty_id) values (1,1);
> > > > However, there is no student_id or faculty_id greater than 2 yet, but
> > > > this statement still executes from mysql:
> > > > insert into student_faculty (student_id, faculty_id) values (3,1);
> > > > whereby it shouldn't since it does not inforce integrity based on the
> > > > foreign keys i have created!

Re: Foreign Key Problem

am 22.10.2006 06:18:10 von coosa

I also can't find it since i use xampp

Peter wrote:
> You could try using the C:\Program Files\MySQL\MySQL Server
> 5.0\bin\MySQLInstanceConfig.exe tool to enable InnoDB.
>
> coosa wrote:
> > Is there a way to plaay around with my.ini file instead of
> > reinstalling?
> >
> > Peter wrote:
> > > First of all, issue the SHOW ENGINES query to determine if you have
> > > InnoDB.
> > >
> > > If not, the easiest way is probably to reinstall MySQL, making sure to
> > > do a detailed installation and selecting InnoDB.
> > >
> > > coosa wrote:
> > > > Ok, i have checked back again and it seems the my storage engine is not
> > > > Innodb but ISAM!
> > > > Is there a way i can change the engine into Innodb?
> > > > I have the default MySql Administrator from mysql.com and phpmyadmin
> > > > and none of them offer a a way to change that
> > > >
> > > >
> > > > coosa wrote:
> > > > > Dear all,
> > > > >
> > > > > I'm new under mysql and have installed mysql5.0.24a community edition
> > > > > for win32.
> > > > > I have tried to implement a foreign key for this following sample
> > > > > scenario:
> > > > >
> > > > > CREATE TABLE student (
> > > > > student_id INTEGER NOT NULL AUTO_INCREMENT,
> > > > > student_name VARCHAR(100) NOT NULL,
> > > > > CONSTRAINT PK_student PRIMARY KEY (student_id)
> > > > > ) ENGINE=INNODB;
> > > > >
> > > > >
> > > > > CREATE TABLE faculty (
> > > > > faculty_id INTEGER NOT NULL AUTO_INCREMENT,
> > > > > faculty_name VARCHAR(100) NOT NULL,
> > > > > CONSTRAINT PK_faculty PRIMARY KEY (faculty_id)
> > > > > ) ENGINE=INNODB;
> > > > >
> > > > > CREATE TABLE student_faculty (
> > > > > student_id INTEGER NOT NULL,
> > > > > faculty_id INTEGER NOT NULL,
> > > > > CONSTRAINT PK_student_faculty PRIMARY KEY (student_id, faculty_id)
> > > > > ) ENGINE=INNODB;
> > > > >
> > > > > ALTER TABLE student_faculty ADD CONSTRAINT student_student_faculty
> > > > > FOREIGN KEY (student_id) REFERENCES student (student_id);
> > > > >
> > > > > ALTER TABLE student_faculty ADD CONSTRAINT faculty_student_faculty
> > > > > FOREIGN KEY (faculty_id) REFERENCES faculty (faculty_id);
> > > > >
> > > > > insert into student (student_name) values ('John');
> > > > > insert into student (student_name) values ('Robert');
> > > > >
> > > > > insert into faculty (faculty_name) values ('Information Technology');
> > > > > insert into faculty (faculty_name) values ('Engineering');
> > > > >
> > > > > Now there are student_id 1 and 2; same goes for faculty_id
> > > > > so this statement should be ok:
> > > > > insert into student_faculty (student_id, faculty_id) values (1,1);
> > > > > However, there is no student_id or faculty_id greater than 2 yet, but
> > > > > this statement still executes from mysql:
> > > > > insert into student_faculty (student_id, faculty_id) values (3,1);
> > > > > whereby it shouldn't since it does not inforce integrity based on the
> > > > > foreign keys i have created!