Adding foreign key columns to a MySQL table: step-by-step guide

Question

I have a table named payment_request in MySQL

DESCRIBE payment_request provides the following output,

The orderbook table is provided below,

I want to add the id in the payment_request table in orderbook as the id column (second position) with the name after Foreign key to payment_request_id.

What is the SQL used to run MySQL?

Answer 1

You can do this when creating the table:

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

Or by changing the form:

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

Also see this tutorial.

Answer 2

First, you need to add a new column to the table orderbook

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

Then add a constraint that defines the foreign key

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

refer to:

• MySQL change table