You have so far created two tables and you should be aware of a clear link between the data in the CD table and the data in the track one. The tracks are on the CD. A CD can have many tracks.
Relationships between tables are crucial to almost all on-line databases. Forums have many posts. People buy many items.
The database server is not going to see a relationship for itself so you need to tell it when there is one.
- select the database on the left by clicking on it's name (intsql)
- click on Designer at the top (if you can't see it click on More first then Designer)
- if necessary drag firsttable to the left and secondtable level with it but to the right
- in the bar of small icons (normally on the left but used to be at the top) look for this icon:
- hover over it to see what it does
- click on it and then move your cursor away from it so that the words vanish
- click on cdReference in firsttable
- click on cdReference in secondtable
- click on OK to create the relationship
Now panic because nothing happened (or you got an error) and you think you messed up! You didn't because to create a relationship you need some things to be right first (some versions tell you which is the problem and some don't). If you ever fail to see a relationship you need to check these things:
- both tables must use InnoDB (or another engine which supports relationships)
- if there is any data in the tables it must match up (if there is a cdReference 2 in secondtable it must match an entry 2 in firsttable)
- the first field you clicked on (cdReference in firsttable) must be the primary key of that table
- the field in secondtable you then clicked on must be the same type as the first one (including length and SIGNED/UNSIGNED)
- the second field you clicked on must be indexed
It is only the last one which you have not done yet (unless you did make a mistake):
- click on secondtable on the left of PHPMyAdmin (if it is not shown click on the database name on the left then on secondtable on that page)
- click on Structure at the top
- find cdReference and in that row click on Index
- press OK after noting the SQL query which should make some sense (you could have made your own page to do this job)
You should not see much difference but if you click on Indexes part way down the page and towards the left you should see your new index (after the primary key index). Indexes in general and primary keys will be explained soon.
Now click on the database name (intsql) on the left, then on Designer and try to create the relationship again. It should work this time and you can tell because you get a new pop up with two drop downs (if not check the bullet list above for what might be wrong). This will be explained soon. For now choose CASCADE for both. Press OK.
You should end up with this:
The green line shows the new relationship visually. It could be said as "One CD on the left can be related to many tracks on the right" or "One CD has many tracks". This is normal for relationships in databases.
Now go back to secondtable. Click on the Insert tab and try to enter a track for a CD which does not exist. You should either get a warning about a foreign key constraint or not even be able to enter a CD which does not exist. Telling the database server about the relationship has made it impossible for you to accidentally add tracks for a CD which does not exist.
Go to firsttable and add a third CD (make it up as it will be deleted soon). Go to secondtable and add two tracks for that CD. Under Browse you should now see six tracks for three CDs.