Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

In MySQL, the FOREIGN KEY on one table is used to point a PRIMARY KEY in another table.

We have two tables with the following values.

    user

id  first_name                  last_name
.......................................
1   John                        Simp                 
2   Chris                       Hely                   
3   Joy                         Roy                      
4   Jenny                       Mill                                           

    user_address

d       place        user_id
............................
1       Canada        
2       Ottawa
3       London
5       India

In the above tables "user_id" in the "user_address" table points to the "id" column in the "user" table, where the "id" column in the "user" table is the PRIMARY KEY in the "user" table and the "user_id" column in the "user_address" table is a FOREIGN KEY in the "user_address" table.

We cannot insert invalid data into the foreign key column as the FOREIN KEY constraint prevents that because the data must be exist in the primary key column on the another table it points to.

FOREIGN KEY Constraint on CREATE TABLE

The following statement creates a FOREIGN KEY constraint on the "user_id" column when the "user_address" table is created:

Example:

CREATE TABLE user_address
(
id int NOT NULL,
place int NOT NULL,
user_id int,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES user(id)
);

We can also define FOREIGN KEY constraint on multiple column, for this use the below statement:

CREATE TABLE user_address
(
id int NOT NULL,
place int NOT NULL,
user_id int,
PRIMARY KEY (id),
CONSTRAINT fk_user_address FOREIGN KEY (user_id) REFERENCES user(id)
);

FOREIGN KEY Constraint on ALTER TABLE

We can also create FOREIGN KEY constraint on a column when the table is already created, for that we use ALTER command. The below statement creates a FOREIGN KEY constraint on the "user_id" column when the "user_address" table is already created

ALTER TABLE user_address
ADD FOREIGN KEY (user_id)
REFERENCES user(id);

Similarly we can define FOREIGN KEY constraint on multiple columns when the table is already created as below:

    ALTER TABLE employee
    ADD CONSTRAINT fk_user_address 
    FOREIGN KEY (user_id)
    REFERENCES user(id);

To DROP a FOREIGN KEY Constraint

We can also drop a FOREIGN KEY constraint. To drop a FOREIGN KEY constraint, use the below statement:

    ALTER TABLE Persons
    DROP FOREIGN KEY fk_user_address;

Hope this will help you :)