mysql tables relationships

[itazev]

Hi there!

 

I recently migrated from ms-access to php/mysql ("a small step for the men, a huge step for the humanity"  ) and now I'm facing an issue on tables relationship . I don't know too much of sql language, so I googled and searched in on books for mysql relationships InnoDB. I found some slight explanations. In a few words, how can I set up a relationship between two tables whether (lets say...)

 

table clients

- clientID

- client_name

 

table products

- clientID

- product_name

 

In this case i'd like to link clientID from clients to clientID on products and on deleting a client, its products will be deleted automatically, also a product entry must have an associated entry on clients.

I was thinking about a database modeling software but i want to learn the sql statements.

 

I appreciate your help!

 

 

 

[veridicus]

What you're looking for are foreign keys and triggers.  Foreign keys can ensure referential integrity (i.e. a record in table x must have a matching value in table y).  Triggers can be used to delete from one table when a deletion is done from another.  Check your mysql version to see which features are available (has mysql ever had triggers?).

 

MS Access has foreign keys.  You create them when viewing "Relationships" and draggings fields across tables so you have lines matching them up.

[Wildbug]

See the multi-table syntax for the DELETE (link) command.

 

Something like (untested):

DELETE clients,products FROM clients,products WHERE clients.clientID=products.clientID;

[veridicus]

Yeah, I definitely prefer all of my deletes to come directly from the application rather than database triggers (at least for web apps).