Home / Questions / 32312
Accepted
Pavlo B.
Asked: 2020-11-08 08:07:42 +0800 CST 2020-11-08 08:07:42 +0800 CST

ALTER TABLE with new column that is foreign key MYSQL

  • 772

I need to add a new column in one of my tables, but this column in turn is a foreign key of another table but I don't know how to do it...

I tried this but obviously it gave me an error:

ALTER TABLE ordenes ADD COLUMN ticket varchar(50) FOREIGN KEY (ticket) REFERENCES tickets(ticket)
mysql

2 Answers

  1. Best Answer

    First add just the column and then the fk:

    ALTER TABLE ordenes ADD ticket VARCHAR(50) NOT NULL;
ALTER TABLE ordenes ADD CONSTRAINT fk_ticket FOREIGN KEY (ticket) REFERENCES tickets(ticket);

    In the tickets table it would be better to call it "something" + id instead of "ticket". Remember that it has to be a unique field.

    • 7

  2. The syntax you are using is not supported by mysql. Add the column and constraint separately, this will work on most engines, not just MySQL:

    ALTER TABLE ordenes ADD COLUMN ticket varchar(50);
ALTER TABLE ordenes ADD constraint FK_Ordenes_Tickets FOREIGN KEY (ticket) REFERENCES tickets(ticket)

    I've added a name to the constraint, but you could skip that part.

    • 2