How to add a foreign key column in the MySQL table?

Question

I have a table named payment_request in the MySQL and the

DESCRIBE payment_request provides the following output,

The oderbook table is provided below,

I want to add the id from the payment_request table in the orderbook as the foreign key with the name as payment_request_id after the id column (2nd position).

What will be the SQL to run the MySQL?

Answer 1

First you need to add new column in table orderbook

ALTER TABLE orderbook
ADD payment_request_id INT(10) unsigned AFTER ID;

Then add a contraint that will define the foreign key

ALTER TABLE orderbook
ADD CONSTRAINT fk_orderbook FOREIGN KEY (payment_request_id) 
REFERENCES payment_request (id);

Reference:

• MySQL Alter Table

Answer 2

You can do this at table creation:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
); 

or by altering the table:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); 

Also refer to this tutorial.