4

I have a table named payment_request in the MySQL and the

DESCRIBE payment_request provides the following output,

enter image description here

The oderbook table is provided below,

enter image description here

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?

Arefe
  • 11,321
  • 18
  • 114
  • 168
  • 1
    Possible duplicate of [Add Foreign Key to existing table](https://stackoverflow.com/questions/10028214/add-foreign-key-to-existing-table) – Sam M Feb 09 '18 at 05:11
  • Let's keep it as mentions that we need to match the variable type for the declaration of the foreign key. – Arefe Feb 09 '18 at 05:14

2 Answers2

6

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:

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • The last 3 commands to add the foreign key doesn't execute and I get the error: `Error Code: 1215. Cannot add foreign key constraint` – Arefe Feb 09 '18 at 04:44
  • @Arefe, because the data type are not matching. see my updated answer. – John Woo Feb 09 '18 at 04:47
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.

Neuron
  • 5,141
  • 5
  • 38
  • 59
Mahi Parmar
  • 515
  • 3
  • 10
  • 31