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
