Foreign Key is different than primary key

Expand Post »

I have created two table by generating via forms. My question is although second table's foreign key appears on phpmyadmin it doesnt have that same value with the first table's primary key.
currently foreign key has all zero values in all of the rows.
Table codes:

php Syntax (Toggle Plain Text)
$sql2='CREATE TABLE IF NOT EXISTS CourseList(
Course_id  int NOT NULL auto_increment,
CourseCode varchar(10) NULL,
CourseName varchar(40) NULL,
Instructor varchar(40) NULL,
PRIMARY KEY(Course_id)
)';
 
$sql3='CREATE TABLE IF NOT EXISTS StudentAccess(              
StudentNumber varchar(12) NOT NULL,
StudentFirstName varchar(15) NOT NULL,
Course_id int NOT NULL,
FOREIGN KEY (Course_id) REFERENCES CourseList(Course_id) ON DELETE CASCADE
)';
$sql2='CREATE TABLE IF NOT EXISTS CourseList(
Course_id  int NOT NULL auto_increment,
CourseCode varchar(10) NULL,
CourseName varchar(40) NULL,
Instructor varchar(40) NULL,
PRIMARY KEY(Course_id)
)';

$sql3='CREATE TABLE IF NOT EXISTS StudentAccess(              
StudentNumber varchar(12) NOT NULL,
StudentFirstName varchar(15) NOT NULL,
Course_id int NOT NULL,
FOREIGN KEY (Course_id) REFERENCES CourseList(Course_id) ON DELETE CASCADE
)';

how should i write foreign key code to link "StudentAccess's Course_id" to "CourseList's Course_id"??

Last edited by peter_budo; Jan 6th, 2009 at 2:01 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Similar Threads

Foreign and primary key in MS SQL
Help with composite and Foreign Keys in Database Design
Total noob question in Database Design
Foreign Keys in MySQL
change primary key in MS SQL

SOB224

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jan 2009

Permalink

Jan 3rd, 2009

Re: Foreign Key is different than primary key

I notice on the Foreign key explanation from the MySQL manual that their tables are INNODB.

http://dev.mysql.com/doc/refman/5.1/...nstraints.html

DiGSGRL

Reputation Points: 10

Solved Threads: 4

Light Poster

Offline

45 posts
since May 2008

Permalink

Jan 3rd, 2009

Re: Foreign Key is different than primary key

i still can't make it.

somehow FOREIGN KEY (Course_id) REFERENCES CourseList(Course_id) ON DELETE CASCADE doesn't work.

it also work but StudentAccess table's Course_id is all zeros.
i also tried Course_id int NOT NULL REFERENCES CourseList(Course_id) but it doesn't work too.

SOB224

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jan 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: difference between IE and Firefox

Next Thread in PHP Forum Timeline: How to assign Menu lists' values to variables?

DaniWeb Message

Cancel Changes

User Name
Password