How to add a Foreign key to an already created table

Question

I’m a beginner in mysql and can’t add a fk to a table I created. I have two tables (person, object) and I want to create a 'fk_person' in object that receives the value of the 'id' attribute of person. Tables are already configured to use the Innodb engine. I have typed the following:

    ALTER TABLE objeto ADD CONSTRAINT fk_pessoa FOREIGN KEY(id) REFERENCES pessoa (id);

So give me back this:

ERROR 1072 (42000): Key column 'id' doesn’t exist in table

I tried to follow the pattern of this video: https://www.youtube.com/watch?v=lxUe5pttK5U "Creating Tables in Mysql 5 with Foreign Keys"

and also tried to follow the example of devmedia http://www.devmedia.com.br/criando-uma-chave-estrangeira-no-mysql/20299

My object table is like this:

    id_objeto int(4) PRIMARY KEY auto_increment  
    fk_pessoa int(4) NOT NULL

My person table looks like this:

    id int(4) PRIMARY KEY auto_increment

Thanks in advance

Answer 1

You are trying to add a rule in a column that does not exist(see what the error is saying).

You must apply to constraint to an existing column of the same type of which it will be a foreign key.

The syntax of the command is:

ALTER TABLE nome-da-tabela ADD CONSTRAINT nome-da-constraint 
FOREIGN KEY(nome-da-coluna-local) REFERENCES nome-da-tabela-da-fk(coluna-fk)

Hence, the correct way you apply to constraint is:

ALTER TABLE objeto ADD CONSTRAINT id_fk_pessoa
FOREIGN KEY(fk_pessoa) REFERENCES pessoa (id);

But for it to be accepted, the columns must be identical. By the structure of the tables you posted, perhaps running alter table below before adding to constraint above, the error is no longer triggered:

alter table objeto modify fk_pessoa int(4)

If it still doesn’t work, as you don’t have data in both tables, it is easier you can erase the tables and recreate them again, already with the constraint right in the create table:

create table pessoa(
    id int(4) PRIMARY KEY auto_increment
    );

create table objeto(
    id_objeto int(4) PRIMARY KEY auto_increment,
    fk_pessoa int(4) NOT NULL,
    foreign key(fk_pessoa) references pessoa(id)
    );

Answer 2

Solved - The Primary key of the 'person' table was unsigned so there was a data type incompatibility. But I’ve been able to clear up a lot of misgivings about a foreign key, so thank you very much!