MySQL help - creating PK to FK link - brand new to Databases

[413x]

Ive made my first database in MySQL,
ive learnt how to create tables, some of the guidance around primary keys (mainly, if its not under your control, dont use a string), loading in CSVs etc

ive come to a problem that i am unsure of as to how to proceed.
apologies in advance if i have written things in the following that are not conventional



lets say i have 2 tables tbl_a and tbl_b
tbl_a is top level, and each record links to many in tbl_b


i have my tables each with a primary key (PK as an auto increment integer)

tbl_a has a PK a_id
but it also has a unique index top_id (i didnt make this a PK as it is possible it could change)

tbl_b has a PK b_id
it also has a unique index bottom_id (i didnt make this a PK for same reason as above)
and has top_id (not a unique index as the link is one to many from tbl_a)

what it doesnt have is a copy of a_id to create what i gather would be a typical PK-FK link


in this situation do i,,,

create a link from top_id to top_id between the two tables when neither are keys
somehow create a field for a_id in tbl_b and then have a_id in tbl_b as a FK - i expect this is the correct procedure?

Apologies if this doesnt make sense!

Edit,
i think i am right in needing this reference as every row in a tbl_b MUST refer to a SINGLE row in tbl_a

also i did toy with the idea of making top_id in tbl_a a PK and top_id in tbl_b an FK, this would have made this problem trivial, but as said, top_id isnt set in stone, but will always be unique ..but this seesm to be a bad idea, hence not doing it

 

[rexehuk]

Not sure exactly what you're getting at... but it sounds like

Table A hasMany Table B

Therefore, table B should have tablea_id as a column. And each item in TableB will have a valid TableA.ID.

Very basic stuff!

 

[413x]

yes, table b should have a tablea_id as a column

how do i create this column?

 

[rexehuk]

Edit the schema!

If you want to be dirty, use PHPMYADMIN, or if you want to do it super easy grab Heidi SQL and change it remotely.

If you're doing the old school way via MYSQL prompt you'd do something like

ALTER TABLE table_b
ADD tablea_id string

More here - http://www.w3schools.com/sql/sql_alter.asp

 

[413x]

oh i know how to add a column, i was wondering if there was a way to populate this column with the tablea_id values .. and yes, im only aware of the old school way (i started learning this on wednesday, first time ive touched a database)

 

[rexehuk]

What we talking here? In terms of how is the database managed? Is it sitting behind an ORM or are you quite literally using a MySQL database directly? If so I've not got direct experience with MySQL relationships, but know you can create them (even with drag/drop Access style in phpmyadmin).

 

[413x]

its literally just a mysql database, i am very new to this, and its the core concepts, rather than the manipulation of the data i struggle with

what i have,

i downloaded MySQl workbench and installed it, and have been working through a tutorial in parallel in creating my own
i do find that the tutorials are more to do with interacting with a database than how they work and all the concepts around them that are foreign to someone such as myself

 

[Deleted member 66701]

i was wondering if there was a way to populate this column with the tablea_id values

Write a query and execute it.

 

[413x]

im not sure how to interpret the error im getting, but im using this

insert into tbl_b (a_id)
select a_id
from tbl_a
inner join tbl_b
on tbl_a.top_id = tbl_b.top_id;

if i run the query from the select staement it produces exactly what i want to ad to the tbl_b.a_id but adding the insert into line creates an error

 

[kaku]

Draw a database diagram.

It looks like you already have an FK relationship with top_id. It's not 100% clear what you're trying to do.

 

[413x]

this might be where i am missing a concept

top_id is a natural relationship, and it exists in both tables naturally, but it is an ID that can change (like the example they give in tutorials where USSR changed to russia)
AND i thought that ideally you should link a PK to a FK (top_id isnt a PK as it may change)

due to the above i thought i should generate a_id in tbl_a (done) make this a PK (done) and link it to a_id in tbl_b. (which i am struggling to populate)

essentially a_id and top_id are equivalent, but top_id may change(due to external factors), but a_id wont

Have i misunderstood convention here?

tbl_a on left
tbl_b on the right
farm_id = a_id
id_farm = top_id
shed_id = b_id

the relationship i tried to establish is there, but tbl_b.farm_id (tbl_b.a_id) is unpopulated

 

[fretted]

What are you trying to do? Why have you got farm_id and id_farm fields in both tables? Are you inserting new rows or updating existing rows in table B? I'm so confused, where am I?

 

[413x]

farm_id and shed_id are just auto_incremental integers

i am trying to populate farm_id in right table with the correct values from the left - inserting a column of data

I have added an excel screenshot that hopefully makes things clear
the yellow is the data that is missing, but i want to populate

that would make a link between tbl_a.farm_id and tbl_b.farm_id

 

[kaku]

Ok, it's more clear what you are trying to do now. I think a lot of the confusion is coming from the naming of the fields.

The purpose of FK links is to maintain data integrity without duplicating data. In your diagram above you can see id_farm is duplicated between tables - this is a good hint that your tables are not normalised correctly.

First set out the rules: you have several farms and several sheds. Sheds live on farms, however a shed can only exist at one farm at a time. A farm may have zero or more sheds on it.

From these rules you identified the relationship above: one farm to many sheds. Since a shed can only exist at one farm at a time, all we need is a single field to represent which farm "owns" this shed (the ID of the farm, as a FK link). Let's say you needed to access the "id_farm" value, but only had a shed reference: you would find the record for that shed, check the "farm_id", and then look up the farm data in the farm table (the SQL can be simplified using joins, but this is the logical process). It sounds long-winded, but this ensures the complete "farm" record stays in one place - this will be a godsend when it comes to modifying values or even adjusting the schema.

The second part I think is not possible/a misunderstanding. From what you've written, you want to auto-populate the FK ("owner") field in the "shed" table. Do you already have source data for this (eg. a document describing "Shed 001A" is at "Farm 002B")? If the "owner" field is blank, it would imply that the particular shed is not yet assigned to a farm.