One to many relationships between primary keys in two tables 2

[oaklandar]

I just started using mySQL server 4.1 and have a Client GUI tool called mySQL Query Browser. My background has always been with Access 2000 and I was able to create one to many relationships with GUI tool between tables.

So if I have TableOne and TableTwo I could create a one to many relationship using the primary keys from each table.

I dont see any GUI tool to do that in mySQL Query Browser. Please advise if this is possible in mySQL?

 

[TonyGroves]

Query Browser doesn't have a graphical design tool. There are various third-party tools available for that, such as DB Designer, free from

http://www.fabforce.net

.

 

[r937]

two tables aren't related by their primary keys -- one of the tables (the "many" table) will have a foreign key that refers to the primary key of the other (the "one" table)

but unfortunately unless you go out of your way to enable relational integrity using InnoDB tables, you can declare foreign keys in mysql but the will not be enforced

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[oaklandar]

Thanks,

I thought two tables would be related by a primary key one to many relationship to another table. How are they related if I do a one to many relationship?

Example:
Customer Table

customer_id  [primary key]

Product Table

product_id   [primary key]
customer_id  [foreign/secondary key]

In my Access database I have customer_id in my Customer Table with a one-to-many relationship with product_id in the Product Table and thought this was relational?

This is what I want to establish in mySQL.

Please advise.

 

[r937]

okay, i see what you're saying

customers is one-to-many with products

however, the relationship is actually between customer_id (pk) in the customer table, and customer_id (fk) in the products table

the relationship is between primary key and foreign key, and not between primary key and primary key

if you actually want to have relational integrity in mysql, you need to use innodb tables

my advice is to read the sections about innodb tables in the mysql manual

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[oaklandar]

Thanks!