I'm struggling with a basic aspect of relational databases. I have a main table that I will be adding all my data too. It contains 4 foreign keys. When I add an entity using the insert into table, do I need to use the foreign keys, or is there some way to use the information that the keys are referring to. For instance
Table Student
-StudentName
-StudentID
-StudentDormID(foreign Key to table Dorm)

Table Dorm
-Dorm Name
-DormID

How would I add a new student and reference to the Dorm table?
Hopefully my question makes sense.
BTW, this is an embedded database, and i'm using sqlite not mysql. I figure the concepts should be the same.



Foreign keys in MySQL are really used only in InnoDB tables to maintain data integrity. See http://www.wellho.net/forum/The-MySQL-Relational-Database/Foreign-Keys-steps-to-enable.html for a further discussion and example.

I'm not yet familiar enough with SQLite to answer for it.

D1sc0rd - thank you for contacting me to confirm your email address as genuine. I "call a spade a spade", and will naturally assume that others do too. So your address shouted at me that and emails sent to it would be discarded. Since we require to be able to contact board members through a "non-forum" route in an emergency, I had to validate ...


Adding a little here.

To link the data together, you need to join your tables - the word "join" is the magic word rather than "foreign key" although it's so magic you can sometimes leave it out.   I've actually been working on an example - our own library - this morning as I'm in the process of upgrading the application.  I have 5 tables, two of which are:

b_btab - a book table. Fields include title and isbn.
b_stab - a subject table. Fields include subject and level

Both tables include a field called biid - our book internal id.  In the book table, that's a unique identifier.  In the subject table, the same biid may occur multiple times as one book can cover multiple technologies.

The join is performed as follows (if I use the word join)

select title, subject, isbn, b_stab.biid from b_stab join b_btab on b_stab.biid = b_btab.biid
or if I use "magic":
select title, subject, isbn, b_stab.biid from b_stab, b_btab where b_stab.biid = b_btab.biid
and in the case of MySQL they are both said to run equally efficiently.

No problem about the e-mail issue. Thank you for your understanding and personal attention.

So, just to make sure I am understanding this, the join table would be used to create a temp table for gathering data (allowing selection from multible tables) but no for entering data entry.

In effect, yes, it creates a temporary table. For entering data, you'll want to add any new record(s) to the table that has the unique identifier - the dorm, if there are any new dorms in you example, then add in the student(s) with a field that contains a reference to the unique dorm identifier.  You can use auto_increment to provide a unique id, or your own scheme if you prefer.

---