How to add a foreign key to a table already created

Question

How to add a foreign key to a table already created

Navigation

#1 by (6 votes)
#2 by (4 votes)

2

I am a beginner in mysql and can not add a fk to a table I created. I have two tables (person, object) and I want to create a 'fk_person' in an object that receives the value of the 'id' attribute of the person. The 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);

Then it returns this to me:

ERROR 1072 (42000): Key column 'id' does not exist in table

I tried to follow the pattern of this video: link "Creating Tables in MySQL 5 with Foreign Keys"

and also tried to follow the example of devmedia link

p>

My object table looks 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

Thank you in advance

mysql chave-estrangeira

asked by anonymous 29.01.2016 / 21:20

2 answers

6

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

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

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)

So the correct way to apply to constraint is:

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

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

alter table objeto modify fk_pessoa int(4)

If it still does not work, since you do not have data in both tables, it's easier to erase the tables and re-create them, already with constraint direct in 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)
    );

29.01.2016 / 21:35

How to create a cron in Laravel? How do I store multiple values in each "option" of a "select"?

score 4 · Accepted Answer

4

Resolved - The primary key of the 'person' table was as 'unsigned' so there was a data type mismatch. But I have been able to heal many doubts about foreign keys, so I thank you deeply!

01.02.2016 / 18:22