Foreign Keys - steps to enable
Posted by gear28 (gear28), 7 July 2003I 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)??
Posted by admin (Graham Ellis), 7 July 2003Foreign 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).
Posted by gear28 (gear28), 8 July 2003Thanks 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
Posted by admin (Graham Ellis), 8 July 2003Here'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.
Posted by gear28 (gear28), 8 July 2003Thanks 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
Posted by admin (Graham Ellis), 8 July 2003Yep, 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.
Posted by gear28 (gear28), 9 July 2003Thanks 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
Posted by admin (Graham Ellis), 9 July 2003You need to:
a) Insert the seller information
b) do the select, capturing the information returned
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.
Posted by admin (Graham Ellis), 9 July 2003I 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!)
Posted by admin (Graham Ellis), 9 July 2003P.S. Working script on line at http://www.wellho.net/demo/t1.php4
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: email@example.com • WEB: http://www.wellho.net • SKYPE: wellho