SQL how do I insert into a table that has FK?

Navigation
0

How do I insert into in a table where there is an FK?

example this is my structure 

Pessoa
ID (primary key)
nome varchar(20)
id_endereco int not null (foreign key)
_______________________________________
endereco
ID (primary key)
rua varchar(50)
_______________________________________

In SQL I did 

create table endereco (
ID int primary key NOT NULL,
rua varchar(50)
);

create table pessoa (
ID int primary key NOT NULL,
nome varchar(50) NOT NULL,
id_endereco int NOT NULL,
CONSTRAINT fk_idendereco FOREIGN KEY(id_endereco) REFERENCES pessoa(ID)
)

Then in the activity ask me, add 20 people ... the problem is that when I use "insert into person" I can not add the 20 people because of the foreign key, if I did not have it, I could get it right .. but as it exists in the person table, that error appears. >

Is there any way I can add to the person table, with nothing in the address? if not, what do I do? 

insert into pessoa (id,nome,id_endereco)
values (1, "Jo Legendary", 1)
    
asked by anonymous 10.10.2018 / 10:18

2 answers

1

Because id_endereco is a foreign key, it needs the reference to exist. So to insert a person with id_endereco = 10 , it is necessary that in the endereco table there is a record with id = 10 .

To insert a person you will need to perform 2 steps:

  • Insert a record in the endereco table: 

    INSERT INTO endereco (ID, rua) VALUES (1, 'Rua Guanabara');

    Note that the ID field is a primary key ( PRIMARY KEY ), that is, you can not have any records with the same value. So for each address it is necessary to enter a ID different, one way to avoid this is to set the field to AUTO_INCREMENT .

  • Insert a record in the pessoa table: 

    INSERT INTO pessoa (ID, nome, id_endereco) VALUES (1, 'Roberto de Campos', 1);

    id_endereco is being filled with 1 , which is the ID for that person's address in the endereco table. If the ID table endereco field was AUTO_INCREMENT , you would replace 1 with @@IDENTITY , which would return the last number entered.

    • By completing these two steps you will have the person's record in the database.

    Example without AUTO_INCREMENT .

    Example with AUTO_INCREMENT .

        
    10.10.2018 / 16:48
    0

    You stated your problem in the question "% w /% of another table, which is still empty" (before editing). If you want to use a id_endereco int (foreign key) in the insert with a value greater than zero (0) you must first add it to the other table (address).

    For example, if you want to insert id_endereco into the id=1, nome="teste", id_endereco=9 table, you must first insert into the pessoa table the register with endereco so that the association can be made.

    You can also insert without reference to id=9 , partially solving your problem, simply enter without this field: 

    INSERT INTO pessoa ( Id, nome )
VALUES (1,"Pessoa1");

    More will stay with the value of id_endereco to zero (0), which is the same as doing: 

    INSERT INTO pessoa ( Id, nome, id_endereco )
VALUES (2,"Pessoa2",0);

    Note: This second form only works if strict mode is not active     

    10.10.2018 / 11:49
    Chrome bot error - ERROR: platform_sensor_reader_win.cc JQuery HTML Currency Mask (non-input)