How do i add a foreign key constraint on an existing column mysql

Question

I am getting the Foreign Key is not correctly formed error on the following code. What is my error, what should i be doing? A factura cand have one or more comanda, a comanda cand have only one factura.

create table comanda    
             (id int(10) not null,
             data_plasare date,`
             metoda_livrare enum('Ridicare Personala','Domiciliu','Punct de livrare','Easy Box'),
             metoda_plata enum('numerar','card la ghiseu','card online','transfer bancar','rate'),
             id_client int(5),
             foreign key (id_client) references client(id));

create table factura
        (id int(5) not null,
         id_produs int(5),
         id_comanda int(10),
         cui_firma varchar(20));

alter table comanda 
add column id_factura int(5);  

alter table comanda 
add foreign key (id_factura) references factura(id);

You are referencing a table named `client` but I don't see it defined in your code. Did you define the table `client` before you tried to create `comanda`? The referenced table must exist before you can make a foreign key refer to it. — Bill Karwin, Dec 02 '19 at 23:07

a student · Answer 1 · 2019-12-02T23:25:29.340

I tried to simplify the problem so we can focus on the main problem. I think the main problem is that when we add a foreign key constrant, we have to know which row in the foreign table we refer to.

I previously added a primary key but that is not necessary. All we need to do is ensure that the id in the parent_table is unique.


create table child_table (`id` int(10) not null);

create table parent_table (id int(5) not null, second_column varchar(20));

alter table child_table add column parent_id int(5);  

insert into parent_table values (1, "first");
insert into parent_table values (2, "second");
insert into child_table values (1, null);

ALTER TABLE child_table 
ADD CONSTRAINT fk_parent_id 
FOREIGN KEY (parent_id) 
REFERENCES parent_table(id);

and I am able to reproduce the problem http://www.sqlfiddle.com/#!9/9f6286

and we can solve this by making the id in parent_table a primary key

create table child_table    
             (`id` int(10) not null);

create table parent_table
        (id int(5) not null primary key,
         second_column varchar(20));

alter table child_table
add column parent_id int(5);  

insert into parent_table values (1, "first");
insert into parent_table values (2, "second");
insert into child_table values (1, null);

ALTER TABLE child_table 
ADD CONSTRAINT fk_parent_id 
FOREIGN KEY (parent_id) 
REFERENCES parent_table(id);

You can also declare the ID in the parent_table as unique. That will work as well.

create table child_table    
             (`id` int(10) not null);

create table parent_table
        (id int(5) not null unique,
         second_column varchar(20));

alter table child_table
add column parent_id int(5);  

insert into parent_table values (1, "first");
insert into parent_table values (2, "second");
insert into child_table values (1, null);

ALTER TABLE child_table 
ADD CONSTRAINT fk_parent_id 
FOREIGN KEY (parent_id) 
REFERENCES parent_table(id);

score 0 · Answer 2 · edited Dec 03 '19 at 00:02

What I can assume from your question is that you want to connect factura table with comanda table. If that's correct, then you need to make id of factura table as primary key.

alter table factura add primary key (id);

or, you can add naming of a primary key constraint , like this

alter table factura add constraint factura_id_pk primary key(id);

Also, to connect both tables, you need to make changes in comanda table as well -

**create table comanda    
             (id int(10) not null,
             data_plasare date,`
             metoda_livrare enum('Ridicare Personala','Domiciliu','Punct de livrare','Easy Box'),
             metoda_plata enum('numerar','card la ghiseu','card online','transfer bancar','rate'),id_factura int(10),


             foreign key (id_factura) references factura(id));**

Hope this works for you !

How do i add a foreign key constraint on an existing column mysql

2 Answers2