Mysql foreign key

I want to make a link between a table customer and a table product by an IdProduct开发者_如何学JAVA.

Example:

Create table customer(
idcustomer  INT not null,
name        Varchar(20),
idproduct   INT,
);

create table Product(
idproduct INT not null,
nameProduct varchar(40)
);

How can I link the two together like the foreign key system for, when I select a customer, I can get all his products? It's a question about the structure of the database.

You want to introduce a 3rd table to resolve the many-to-many relationship between customers and products. It should consist of idcustomer and idproduct.

Then, to get all the products for a given customer:

SELECT c.name, p.nameProduct
    FROM Customer c
        INNER JOIN CustomerProductXref cpx
            ON c.idcustomer = cpx.idcustomer
        INNER JOIN product p
            ON cpx.idproduct = p.idproduct
    WHERE c.idcustomer = 12345

In mysql a foreign key is a special type of constraint. It is preferably created with the table, but can also be added afterwards. In this case, you might define the constraint as:

ALTER TABLE customer ADD FOREIGN KEY (idproduct) REFERENCES Product (idproduct);

(Note that you have to use the InnoDB engine to take advantage of FK's in mysql. More here

However FK's aren't required to make a JOIN, which is how you would link the tables in a SELECT -

select c.idcustomer, c.name, p.nameproduct
from customer c
join Product p on p.idproduct=c.idproduct;

Here's how you'd make a foreign key constraint (ignoring the cardinality issues that Joe rightly suggests):

CREATE table Product(
    idproduct INT not null,
    nameProduct varchar(40),
    PRIMARY KEY (idproduct )
);  

CREATE table customer(
    idcustomer  INT not null,
    name        Varchar(20),
    idproduct   INT,
    FOREIGN KEY (idproduct) REFERENCES Product(idproduct )
);

Get your data like this:

SELECT * FROM Product AS P
INNER JOIN Customer AS C ON C.idproduct = P.idproduct
WHERE C.idcustomer = 1