List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 26 1999 6:38pm
Subject:Re: Foreign Keys (once more :) )
View as plain text  
Hi APoorva


"Geocrawler.com" wrote:
> 
> I have rade the manual and understand what foreign keys are and why mysql doesnt
> support them.
> 
> What I dont understand is how do i connect two tables or more where the record in one
> table is connected to the primary id of the first.

You use JOIN's for this approach.

> We  are begining a very big project and refrencing one table to another is very
> important. THere will be about 70 different tables so the need to clarify one doubts
> before plunging is the best way.
> 
> 1) How  would one go about doing this?

Example:
SELECT * FROM firstTable JOIN secondTable WHERE firstTable.ID = secondTable.firstID;

> 2) Is this table structure valid in MySQL
> 
>                         table cust_name
>                         cust_id primary key
>                         name string
>                         etc.
> 
>                         table cust_job
>                         job_id primary key
>                         cust_id from cust_name (this will not be decalred as foreign
> key)

Certainly, just use:
CREATE TABLE 
	cust_name 
	( cust_id INTEGER NOT NULL PRIMARY KEY
	, name VARCHAR(40)
	, ....
	);
CREATE TABLE
	cust_job
	( job_id INTEGER NOT NULL PRIMARY KEY
	, cust_id INTEGER NOT NULL
	, ....
	);

>  3) I have gone thorugh the join statment and some of the mailing list articles
> pertaning to join statement. WIll I have to join thee tables every time somebody acces the
> database, like have the code inbuilt in the php page? Or can one just join the tables in
> mysql nad leave it without joining them everytime a user logs in or accesses the
> database.
> 

JOIN's are alway a one time thing.
They are not stored anywhere, so you have to JOIN in each query you need it.
If you have speed problems, then you always can create a table with all the infos from
multiple tables in it.
In my experience joining up to 3 tables is very fast.
But YMMV, because this depends on the size of the tables and the queries you run across
them.

> When does foreign keys come into existance in MySQL?

If you mean the trigger part: never.
If you mean remembering the foreign settings: in 4.xx I think.(This means not this year).

> Thanks for time and help. Thanks in advance.
> 
> APoorva

Tschau
Christian

Thread
Foreign Keys (once more :) )Geocrawler.com22 Jul
  • Re: Foreign Keys (once more :) )Christian Mack26 Jul