473,324 Members | 2,535 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,324 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 7274
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...
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
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...
1
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.