Primary / Foreign Key.. how do I?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pistolfire99
Forum Commoner
Posts: 58
Joined: Thu May 23, 2002 3:18 pm

Primary / Foreign Key.. how do I?

Post by pistolfire99 »

Hello All,
Lets say I want to create a database with 3 tables in it and it looks something like this. The name of the database is memberDirectory

MySQL version:- 3.23.54
OS:- Win 2K Pro
Apache 1.3.xx + PHP 4.2.3

Code: Select all

CREATE TABLE tblPrimary 
(
  emailAddress varchar(100) NOT NULL default '',
  password varchar(16) default NULL,
  password2 varchar(16) default NULL,
  UNIQUE KEY emailAddress (emailAddress)
) TYPE=MyISAM;


CREATE TABLE tblMember 
(
  memberID mediumint(9) NOT NULL auto_increment,
  emailAddress varchar(100) default NULL,          <--- I want this to be a foreign key to this table
  lastName varchar(50) default NULL,
  firstName varchar(50) default NULL,
  PRIMARY KEY  (memberID)
) TYPE=MyISAM;


CREATE TABLE tblFamily 
(
  familyID mediumint(9) NOT NULL auto_increment,
  memberID mediumint(9) default NULL,       <--- I want this to be a foreign key to this table
  emailAddress varchar(100) default NULL,    <--- I want this to be a foreign key to this table
  firstName varchar(50) default NULL,
  maritalStatus varchar(15) default NULL,
  Occupation varchar(25) default NULL,
) TYPE=MyISAM;
Now my question is, how do I do it such that, the given tables has primary/foreign relationship. Any ideas or help would be appreciated. The actual number of fields is a bit more then what I have here, also I might have a few more tables. I also cannot run MySQL 4.0 or later, since my server is running the above configuration on Linux.

PS:- I also tried to convert the tables to "InnoDB" using the query editor in phpmyadmin and MySQL Control Center and somehow it still registers the tables as MyISAM.
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post by f1nutter »

A foreign key is a primary key from another table used in a join. You cannot set a key to be foreign.

See http://www.databasejournal.com/sqletc/a ... _1469521_2

Maybe you mean a unique field, which does not need to be primary, like email from tblMember?
pistolfire99
Forum Commoner
Posts: 58
Joined: Thu May 23, 2002 3:18 pm

Post by pistolfire99 »

f1nutter
A foreign key is a primary key from another table used in a join.
Hey,
Thats Exactly what I am trying to do here. How do I tell MySQL that emailAddress.tblMember is a foreign key to the table tblPrimary and so on...
I made all the tables with the above fields but now I dont know how to make that relationship.

BTW, that link was helpful. Thank You for help n would be great if this query gets solved.
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post by f1nutter »

You don't explicity need to tell the database that you need a foreign key. It is more a concept then a requirement. As long as you set the right primary keys, and get the joins correct, MySQL will 'see' the foreign key.

You can't program foreign keys, and should'nt worry about them. Concentrate on setting primary keys, and getting the joins right.
pistolfire99
Forum Commoner
Posts: 58
Joined: Thu May 23, 2002 3:18 pm

Post by pistolfire99 »

Hey f1nutter,
Thank You for your input. I am now confused about "joins" ..., I think I will go and buy a MySQL book. This way I learn a lil and solve my problems. I sincerely appreciate your feedback.
I will post if I need more help and in the meantime if you can say a few words about "join", then that would be cool.
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post by f1nutter »

You could work your way through sqlcourse.com, or if you feel you know all about SELECT, GROUP, HAVING etc, then go straight to http://sqlcourse2.com/joins.html.

Have fun.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Just to add what you guys are talking about, there really is no forieng key support in mysql using myisam table types. What you guys are talking about doing is implementing this stuff on your own. Other DB's implement fk''s for you. Postgres is a perfect example.

Now if you want to use FK constraints in MySQL, use the innobase table types. There is also transactional support and referential integrity as well.

FK is a big issue and it's one of the things that cause a good number of people to not take MySQL seriously. Look at it a bit more before finalizing your approach, but using something that takes care of it for you can save you a lot of coding.

Cheers,
BDKR
pistolfire99
Forum Commoner
Posts: 58
Joined: Thu May 23, 2002 3:18 pm

Post by pistolfire99 »

Hey, Thank You for your input. I have read up about the InnoDB table types, but somehow I am unable to create those in my version of MySQL 3.23.54 and I cannot change my current MyISAM table types to InnoDB. BTW, I am using MySQL Control Center and PHPmyAdmin to Administer MySQL server. I also cannot Update my version of MySQL, since I want my windows 2000 machine (development+testing) to use exactly those server versions as my Linux Box.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Hey pistol,

pistolfire99 eh? Ever own a handgun? When I worked for the California Dept of Corrections, I carried a Ruger P85 MKII off duty. Awesome weapon! Oh well.....

Anyways,
MySQL 3.23.54
You need MySQL Max in the 3.23.xx tree. In versions beyond 4.0, it's included.

:wink:

Cheers,
BDKR
pistolfire99
Forum Commoner
Posts: 58
Joined: Thu May 23, 2002 3:18 pm

Post by pistolfire99 »

NO my friend, I dont own one. I have used a few of them (guns, I mean) during my cadet training, but thats about it... :)
Post Reply