How can I create a relationship between two (or more) tables.

[Addola]

Well it got me confused. I have a table that already have a Primary key let's say HouseID and we have other attributes. I will also have a Foriegn key refering to , say the owner so, I will add OwnerID to the table to refer to an owners id in another table , namely Owner. So then I can query for Houses owned by an owner using something like :

SELECT HouseID, HouseLocation FROM Owner,House WHERE Owner.OwnerID = House.OwnerID ;

........

Is there any special DLL code ?? or what cause I am getting confused.

I will appreciate any help,
Adel

 

[danielhozac]

Uhh.... What? //Daniel

 

[Addola]

well in a standard SQL i would do it this way

ALTER TABLE house ADD FOREIGN KEY (owner) REFERENCES owner(ownerID) ;

===
We have two tables house & owner
house have the following columns :
houseid,houselocation,owner
owner have the following column :
ownerid,ownername,owneraddress

I want to create a relation between those two tables so that when I insert a new house record and i supply as an owner a number that doesn't have a match in "owner" table , it would throw an error and not accept the addition of the record.

You got me ?

 

[danielhozac]

MySQL doesn't support foreign keys, unless you use the InnoDB table type. //Daniel

 

[sleipnir214]

Foreign keys exist for the sole purpose of data consistency. They make sure that you can't put a value, in your specific case, into the column "key" in the table "house" unless that value already exists in some row of column "ownerID" in table "owner".

They don't define any kind of relationship at all. Relationships between tables in a database exist only contextually.

You can do that in your own code. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[Addola]

uhaa I understand that Foreign keys enforce REFRENCIAL INTEGRETY. U mean that I can't do that when defining the tabls BUT I do it when write my code in PHP, Java or whatever to check if the value exist in the other table.

got it


Thank you.

Adel

 

[sleipnir214]

Foreign keys can improve data consistency through the use of referencial intergrity.

But yes, you are correct. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[BigTeeJay]

I have been searching and searching, and so far this thread
has been the most helpful

I have a slight variation on the original posters question.
In MS Access (I know, I know...) or MS SQL (yes.... I know you can setup relationships between tables. This
way you could do something like...

select c.custname, o.orderdate
from customers c, orders o

...if you have the relationship setup in Access/SQL Server
that relates a customerID in the customer table to a FK
in the Orders table.

It sounds like I need to change my table type to Inno so I
can use FKs, but would I then be able to do what I am
wanting?

The reason is, I want to create an interface where users
can select fields from tables without having to know how
to do the relationships (although I would allow them to
specify their joins in actual sql code, if they are
familiar with SQL).

Regards,
Tj

 

[sleipnir214]

BigTeeJay:

You don't need explicit foreign keys to have a relationship between two tables. You can imply them in your queries.

An equivalent to your Access query in MySQL might be:

SELECT c.custname, o.orderdate
FROM customers c, orders o
WHERE c.id = o.customer_id

It is the WHERE clause that tells the server to relate the records of the tables. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[BigTeeJay]

Excellent point, I was both trying to make it far more
difficult that it was, and also forgetting that even if you
have the relationships specified in Access you still have
to include the where clause.

For example (from the Northwind DB)...
SELECT O.CustomerID, OD.UnitPrice
FROM Orders O, [Order Details] OD

...returns all possible combos of the two requested fields,
while...
SELECT O.CustomerID, OD.UnitPrice
FROM Orders O, [Order Details] OD
WHERE O.OrderID = OD.OrderID

...returns exactly what we asked for, 1 record for each
match of the requested fields.


Tj