473,237 Members | 1,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

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 7270
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jeremiah Jacks | last post by:
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using = the pre-compiled binaries. I have a database with INNODB tables. When I insert a row into one of the child tables, I get...
1
by: Andrew DeFaria | last post by:
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB...
2
by: Gunnar Vřyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
1
by: Jason Madison | last post by:
We sometimes get very large databases that we want to cut down to use for testing. The information is all related to a central accounts table. The way I thought of doing this is to grab all...
0
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A...
10
by: Shawn Chisholm | last post by:
Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a...
2
by: Ian Davies | last post by:
I have created a database with about 17 tables. I have been creating foreign keys some of which have worked but when creating others I get the message below ************************* 1005...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
2
by: jarea | last post by:
I have read quite a bit about this error but I have yet to find the solution to my problem. I am trying to execute the following mysql statement: alter table line_items add...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.