Foreign key problems

[Toolmaster]

Hello everyone, I have a problem with a foreign key in one of my tables, I am creating an online booking system for my university and so far I have created two tables: 1. Lecturer table, 2. Lecturer_Login table

 

This is what I am confused with, below is some coding which i've used to create the two tables:

 

LECTURER TABLE

$sql = 'CREATE TABLE Lecturer( lecturer_id VARCHAR(9) PRIMARY KEY, name Varchar(50) NOT NULL, FOREIGN KEY (office_id)

REFERENCES office (office_id), mob_tel_no INTEGER, email_address Varchar(50))';

 

LECTURER LOGIN TABLE

$sql = 'CREATE TABLE lecturer_Login (login_id VARCHAR(6) PRIMARY KEY,

FOREIGN KEY (username) REFERENCES lecturer(lecturer_id), passwrd VARCHAR(15) NOT NULL)';

 

The foreign key "username" is not shown when I query for the description of the table, I don't know why and what I can do about it.  ??? ???

 

Any ideas?

[Toolmaster]

Can anyone help please? 

[Toolmaster]

Basically the situation is this, whenever I want to have a foreign key in the tables, whenever I look into those tables to see the attributes inserted, the foreign keys do not show up.

[revraz]

Are you inserting the values when you do your INSERT statement?

[Toolmaster]

What I have done is just create the tables, no data has been inserted in the tables as of yet.

Now, when I go onto a front end interface of mysql and I type "DESC lecturer_login"

 

there are only two attributes visible, one is the login_id (pk) and the other is the password attribute. The foreign key named username, which references from the lecturer table "lecturer_id" does not appear.  ???

[revraz]

Do you have access to phpmyadmin?

[Toolmaster]

I'm not familiar with it no, I am not a frequent PHP and Mysql coder, but I need to learn if i'm ever going to get a job that would interest me.

[revraz]

I don't usually create a foreign key like that via sql commands, I use phpmyadmin.  You may want to ask Fenway in the MySQL forums.

[revraz]

This might help

 

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

 

http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html

[Toolmaster]

I don't usually create a foreign key like that via sql commands, I use phpmyadmin.  You may want to ask Fenway in the MySQL forums.

 

What is the best version to use?

[Toolmaster]

This is a continuation thread from another thread I created in the PHP help forum....

 

http://www.phpfreaks.com/forums/index.php/topic,179558.msg801064.html#msg801064

 

Any ideas what the problem could be?

[revraz]

You may be thinking you need a foreign key designator on the field when really you don't.  You just have to use JOIN functions when you do a query.

[Toolmaster]

What I want is when I insert a value in the foreign key attribute (username) within the lecturer_login table, it will look at the lecturer table to see if the lecturer_id inserted in the foreign key attribute is existing. If it is existing  then users will be signed in providing their password is correct, if not then they will obviously recieve notification of their login being unsuccessful.

[Barand]

You need to define a "username" column in the table before you can define a FK constraint on it. You need somewhere to store the username.