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

Asked

Viewed 4,117 times

0

How do I make a insert into in a table where there is a 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 asks me, add 20 people... the problem is that when I use "Insert into person" I can’t add the 20 people because of Foreign key, if I didn’t have it, I could get it good.. but as it exists in table person, appears that error.

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

insert into pessoa (id,nome,id_endereco)
values (1, "Jo Legendary", 1)
mysql sql

  • Put the SQL code you are using to do the INSERT. Probably the column does not accept NULL.

  • 2

    Apparently, the FK does not exist, since the outta table has no record with this foreign field. Enter your code to make it easier to identify.

  • 4

    You first have to do the insert on the table endereco, rescue the id and use it to insert the record in the table cliente.

  • Place the structure of your table endereco.

  • @Robertodecampos ready, I more or less refined what I wanted to explain.. sorry, when I asked the question was 5 am I was very tired hehe

  • @Joãomartins yes, the column cannot accept null (I edited the question) in this case it is mandatory to have the address to create the person? , in this activity as I do to add a person with that fk to the address id?

  • @RORSCHACH Good morning! ready put the problem structure and sql I made :D

  • Basically cannot insert a record in the table pessoa without first having a record in the table endereco.

  • @Joãomartins right, so in case I do an Insert in the 'address' by adding an ID and a street, and then I do the person’s Insert by putting id, name, and address id? is that as the activity was to add the person, I didn’t know I had to do address first... because in question 1 it was add '5' people and in question 3 it was add '5' addresses.

  • @Jolegendary maybe the teacher did it anyway so you realize it doesn’t work that way.

  • @Jolegendary can see his INSERT?

  • hi @Jorgeb. thanks for commenting, put at the end of the question as more or less I tried the Insert in the person table... any suggestions? (I’m learning sql now)

Show 7 more comments

2 answers

1


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

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

  1. Insert a record into the table endereco:

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

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

  2. Insert a record into the table pessoa:

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

    The id_endereco being filled in with 1, which is the ID referring to this person’s address in the table endereco. Case the field ID table endereco were AUTO_INCREMENT, you would replace the 1 for @@IDENTITY, that the last number inserted would be returned.

By performing these two steps you will be with the registration of the person in the database.

Example without AUTO_INCREMENT.

Example with AUTO_INCREMENT.

  • Do not specify anything that the reference exists, only a Warning if it is 0 or insert without the reference.

  • Hi, thank you for clarifying the doubt... is it advisable to use the AUTO_INCREMENT in all Primary key? or I may have some kind of problem if use at all.

  • It is not mandatory, it goes from case to case.

0

You stated your problem in the question «id_endereco int (foreign key) There from another table, which is still empty» (before editing). If you want to use one id_endereco on insertion with value greater than zero (0) you have to add first in the other table (address).

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

You can also enter no reference to the id_endereco, partially solving your problem, just insert without this field:

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

But then you’ll get the value of id_endereco to zero (0), which is equal to do:

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

Note: This second form only works if the Strict mode not active

  • ah yes, sorry... when I spoke empty I meant that there were columns, but there was no record in it.

  • @Jolegendary read again, I just edited.

  • Insert without reference will not resolve when you do not enter the value of the INSERT he will take the value DEFAULT(which in case is not defined) and if not defined will assume NULL. But the countryside id_endereco is NOT NULL, then another error would be returned.

  • @Robertodecampos you are mistaken, the default for an integer is 0.

  • I rode a example for you to see.

  • @Robertodecampos this example is without NOT NULL that is different.

  • Put NOT NULL, the INSERT does not work, is returned the error Field 'inteiro1' doesn't have a default value!

  • @Robertodecampos Vamos continue this discussion in chat.

Show 3 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.