I have a main table that references about 5 primary keys of other tables as 'foreign keys'

I am unsure if I have done this correctly. For a primary key, to be referenced correctly as a foreign key in another table, what steps have to be taken?

I use phpMYAdmin also, when setting up the database do the names of the primary key, and foreign key have to be identical?

And say I have a table for contact details, how can I get the value (auto increment) to also be refereced in my main table where the contact details become the foreign key. Is there a way, when the user enters their information, the value (contact_id) to go into the 2 tables at the same time (contact table and main table that references contact_id)??

Help appreciated
cheers
gear

Foreign keys have only limited support / us in MySQL;  they're only available if you're using InnoDB tables, and they're used just to maintain the integrity of data between your tables. Such relationships are not hard to maintain through application logic.

If you do choose to use foreign keys, no, the name of the key  does not have to be identical to the name given to the corresponding primary key.

Since foreign keys just maintain integrity, they don't provide a means by which you can enter data that goes into several tables at a time  (quite the reverse - they can prevent you adding information to a child table when there isn't a matching element in the parent).  


Thanks for the advice.........but I am still a little unclear?

Can I use a primary key value in another table, without formally making it a foreign key? All the foreign keys in my table will be chosen using drop down boxes by the user.

And if I have a table for contact details, how can I then reference that in my main table that talks about their property that they want to sell?

I understand how I can join the tables using SQL queries, but with the contact details table, the primary key, wont be available for the user to select from a drop down box, so how do I get that value into the main table.

I'm sorry that I'm not explaining myself very well, thanks for your help  

Here's a sample query which - I think - provides a sample answer.  We have  two tables - seller and property.  The primary key of seller is the id column,   and that id is the "foreign key" in property, where it's called s_id (short for seller id).  We're interested in who's selling property number 25 - that's based on a pulldown menu selection where the returned value is the property id.

SELECT seller.phone, seller.address, property.address
       FROM seller, property
       WHERE seller.id = property.s_id and property.id = 25
       
Note that I've used "id" for the primary key in both tables, so that the two id fileds do NOT relate; you can argue that this isn't a good idea, but I've done it to make a point.

It's never easy to explain in general terms exactly what you're looking for in tables / relationships;  if my example doesn't quite look like what you need, please post up a "yes but ..." type comment so that I can post a modified example closer to what you're looking for.


Thanks Graham, things are becoming clearer  

The one thing that I am struggling to get a grips on, is how do I get the seller_id value into the s_id column of the property table auotmatically.  I don't want to enter any info into the tables myself, except initially, I want it all done by the user.

My questions basically is.....

when a user fills out the 'seller' table, and is a given an auto increment value in the seller_id column, how can I make it so that when that value is generated it is also entered into s_id table of the 'property' table.

As I said thanks for the help, and I hope my question is clear

Yep, much clearer (and I think we're working in the same direction now) ...  If you're inserting a new seller and his first property for sale:

a) Get all the data from the user
b) INSERT appropriate information into the seller table, and autoincrement the primary key
c) Find out what that key was via SELECT LAST_INSERT_ID()
d) INSERT remaining information into the property table, and that information includes your "foreign key" - the seller's id - returned by step c

To Insert a subsequent property for an existing seller, find his ID via a select statement with an appropriate WHERE clase, and then insert the property details into the property table using that ID.

Aside - I don't know if I explicitly made the suggestion on this thread, but ity would be a good idea to have a primary key on the secondary (property) table, even if you don't see any reason that you would want it at this stage ... it will help you no end later on if, for example, you manage to get the same property details entered twice;   you could then delete one of the two copies, even if all the other fields were identical.

Thanks again Grahame, you've been a great help.

Just one more question....

Where does the select statement go, so that the seller id will go into the property table.

I am interested in the nut and bolts so that this all happens automatically.

Forgive the ignorance, but I'm pretty new to MySQL and SQL

You need to:

a) Insert the seller information
then
b) do the select, capturing the information returned
then
c) Insert the property information (one of the values you'll
specify here will be the result returned by the select).

The exact syntax of the program enclosing the three SQL statements will vary depending on the language that you're using for your page.



I was having to get myself back into MySQL (big course to give tomorrow), so I wrote a complete example - should be useful not only to gear28, but also for future trainees!

It's in PHP.  Two tables that are related - sellers and properties; the SQL commands needed to create the tables are commented into the top of the PHP to make the example complete.

Two forms - the first for data entry, where you can enter properties for sale by any of the existing sellers or give a new seller name.  The second form looks information up (that's away from the immediate question in this thread, but it makes for a complete example!)

Code:

<?php // The following SQL is used to create the tables //  use test; //  drop table if exists seller; //  drop table if exists property; //  create table seller (        //  id int primary key auto_increment,        //  sellername text); //  create table property (        //  id int primary key auto_increment,        //  sid int,        //  propertyname text); mysql_connect("localhost","trainee","abc123"); mysql_select_db("test"); $result = "Your result will appear here"; if ($_GET[which] == 1) {        // Data ENTRY form        if ($_GET[newseller] != "") {                // A New Seller                mysql_query("insert into seller (sellername) values (\"".                        addslashes($_GET[newseller])."\")");                $q = mysql_query("select last_insert_id()");                $sidarr = mysql_result($q,0);                $sid = $sidarr[0];                mysql_query("insert into property (propertyname, sid) values (\"".                        addslashes($_GET[newproperty])."\", $sid)");        } else {                // Subsequent property for existing seller                $sid = $_GET[oldseller];                mysql_query("insert into property (propertyname, sid) values (\"".                        addslashes($_GET[newproperty])."\", $sid)");        }        $result = "Your insert has been completed"; } if ($_GET[which] == 2) {        // List all properties for a seller        $result = "Here are your results:<br>";        $q = mysql_query("select seller.sellername,property.propertyname from seller, property ".                "where seller.id = property.sid and seller.id = $_GET[oldseller]");        $nsales = 0;        while ($row = mysql_fetch_assoc($q)) {                $result .= $row[sellername] . " is selling ". $row[propertyname] ."<br>";                $nsales++ ;        }        $result .= "$nsales properties listed for this seller"; } // Make up a pull down menu for all existing sellers $q = mysql_query("select * from seller");        while ($row = mysql_fetch_assoc($q)) {                $sellers .= "<option value = $row[id]>$row[sellername]";        } ////////////////////////////////////////////////////////////////// ?> <head><title>Related Table setup example</title></head> <body bgcolor=white> Example of a PHP page to enter and retreive data in related tables<hr> Data ENTRY form <form>Please select a seller <select name=oldseller><?php print ($sellers); ?> </select>or enter a new Seller called <input name=newseller><br> Please enter the name of the property for sale <input name=newproperty><br> Then <input type=submit><input type=hidden name=which value="1"></form><hr> Data ENQUIRY form <form>Please select a seller <select name=oldseller><?php print ($sellers); ?> </select>Then <input type=submit><input type=hidden name=which value="2"></form><hr> <?php print ($result) ; ?><hr> Copyright, Well House Consultants 2003 </body>

P.S.   Working script on line at http://www.wellho.net/demo/t1.php4

---