DaniWeb Forum Index > Web Development > PHP

Foreign Key is different than primary key

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2009

Posts: 4

Reputation: SOB224 is an unknown quantity at this point

Solved Threads: 0

SOB224

Offline

Newbie Poster

Foreign Key is different than primary key

Jan 3rd, 2009

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:

 Help with Code Tags 
 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.

•

Join Date: May 2008

Posts: 45

Reputation: DiGSGRL is an unknown quantity at this point

Solved Threads: 4

DiGSGRL

Offline

Light Poster

Re: Foreign Key is different than primary key

Jan 3rd, 2009

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

A little clarification goes a long way.

•

Join Date: Jan 2009

Posts: 4

Reputation: SOB224 is an unknown quantity at this point

Solved Threads: 0

SOB224

Offline

Newbie Poster

Re: Foreign Key is different than primary key

Jan 3rd, 2009

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.