470,636 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

Foreign Key Problem

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!

Oct 18 '06 #1
5 6959
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!
Oct 18 '06 #2
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!
Oct 19 '06 #3
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!
Oct 19 '06 #4
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!
Oct 20 '06 #5
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!
Oct 22 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
2 posts views Thread by Gunnar VÝyenli | last post: by
10 posts views Thread by Bodza Bodza | last post: by
1 post views Thread by Jason Madison | last post: by
10 posts views Thread by Shawn Chisholm | last post: by
2 posts views Thread by Ian Davies | last post: by
1 post views Thread by Korara | last post: by
???
reply views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.