Hi there,

First post to group. I'm just starting out trying to learn mysql to
use alongside php. I'm right at the start and finding it difficult to
grasp the basics of design. I've read about keys and joins but don't
seem to be able to put it into practice.

The question I would like to ask is if you have two tables one with a
unique key of customerid (to be original) and another table with the
customerid as the foreign key how does the second table acquire the
first tables info. Is it automatically added when the foreign key is
created, do you have to add it manually, or is this where the join
comes in?

Sorry its such a basic question but the magic is eluding me.

Many thanks

KC

On Mon, 06 Aug 2007 13:30:38 -0700, KC wrote:
> Hi there,
>
> First post to group. I'm just starting out trying to learn mysql to
> use alongside php. I'm right at the start and finding it difficult to
> grasp the basics of design. I've read about keys and joins but don't
> seem to be able to put it into practice.
>
> The question I would like to ask is if you have two tables one with a
> unique key of customerid (to be original) and another table with the
> customerid as the foreign key how does the second table acquire the
> first tables info. Is it automatically added when the foreign key is
> created, do you have to add it manually, or is this where the join
> comes in?
>
> Sorry its such a basic question but the magic is eluding me.

Nope, the foreign key isn't populated automagically. You have to assign
the value in the first place to the dependant record. But once you do
that, then some additional things *can* happen automagically (depending
on a few different things).

1) You can make child records get deleted automatically when you delete
the parent.

2) You can make child records automatically update to stay with changing
values in the parent record

3) You can restrict the parent record from being delete while there are
still child records attached.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

Thank you Peter,

Is there a best practice for assigning a value? Re entering the
foreign key data could lead to typo errors couldn't it. Sorry I'm
really not as dim as I might seem (although I do feel it).

Regards

KC

> Nope, the foreign key isn't populated automagically. You have to assign
> the value in the first place to the dependant record. But once you do
> that, then some additional things *can* happen automagically (depending
> on a few different things).

On Mon, 06 Aug 2007 22:40:03 -0700, KC wrote:
>
>> Nope, the foreign key isn't populated automagically. You have to assign
>> the value in the first place to the dependant record. But once you do
>> that, then some additional things *can* happen automagically (depending
>> on a few different things).
>
> Thank you Peter,
>
> Is there a best practice for assigning a value? Re entering the
> foreign key data could lead to typo errors couldn't it. Sorry I'm
> really not as dim as I might seem (although I do feel it).
>

We arrange quotes this way here.

If you're creating the value that will be used as the joining value
yourself programmatically, just hang onto the value.

If you're adding a child row later, you can use a subquery to find the
parent record's id.

If you want to put a child record on immdiately after creating the
parent record, you can use the LAST_INSERT_ID() function to retrieve the
value.

If you're typing it in by hand, use a copy-paste routine.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

Thank you Peter. I'll give you suggestions a try.

Many thanks

KC