Insert data in multiple related tables

Asked

Viewed 58 times

0

I started a project at the same time that I started studies in a database. I created a customer phone only table and another table for all customer data, including phone as Foreign key. in the phone table I created a key Primary and used the client id as Foreign key.. When I insert data into client table requires that phone table already has Primary key. Tables were created without problem. but when I enter data, in whatever table, I have problem. how should I do?

table_clientes(
idcliente int auto_increment,
nome varchar(10),
idfone int,
primary key(idcliente),
foreign key(idfone) references table_fone(idfone)
)

table_fone(
idfone int auto_increment,
fone1 varchar(10) not null,
fone2 varchar(10),
celular varchar(10),
primary key(idfone),
idcliente int,
foreign key (idcliente) references tbclientes (idcliente)
)
mysql foreign-key primary-key

  • First, you must have an FK in each table, without it you can’t continue, even if it’s just a symbol. After, create the records in the primary table and after the record linked to the secondary table.

  • 2

    I think you should rethink your data model.

  • @Jaksonfischer, so I should create a PF(worthless) and use it as FK in the other table? Because I’ve already created a FK in each table.

  • @anonimo, can you explain to me where my data model failed? so maybe you can rethink.

  • If you only have one phone of each type per customer, you don’t need a separate table for phones. And if there is more than one, it makes no sense FK on the customer table. FK should be at only one end.

  • @bfavaretto, actually the phone table has phone1,phone2,extension, cell phone.. and so on. Then I should create only a phone table with the idclient to identify and perform query in this table to know the phone?

  • 1

    That’s why I said "one phone of every kind". This way you modeled each row in the table is a "package" with potentially 3 phone numbers. But it’s not the only way to model. The principle is, if you determine that each customer can only have up to 3 phones, the simplest is to create 3 phone columns in the customer table. If the maximum number of phones per customer is undetermined, then yes the only way is to use a separate table. understood?

  • @bfavaretto, got it. It makes sense what you said. I’ll review my modeling. Thank you so much for your help.

Show 3 more comments

1 answer

0


When referencing a Foreign key you must have the id of the record that will do the relation in advance. In your modeling the two tables have Foreign key for each other. This way one prevents the other from inserting as it needs to have the id of the other.

It is not necessary to use Foreign key in both tables. Only one must be referenced.

Well, then which table do I use the Foreign key?

Depends on what relationship you want to have.

For example:

Customer may have more than one phone? So Foreign key goes on the phone table, telling who that phone belongs to

Phone may have more than one customer? So Foreign key goes in the client table, telling which phone the client has.

Customer may only have one phone?

In this condition there are two ways to be made:

  1. Create a phone field in the client table.
  2. Use the Foreign key in the phone table and controlling the rule via code.
  • 1

    I agree with you, Anderson. I was using FK in both tables. I had to redo my data modeling. Thank you so much.

Browser other questions tagged mysql foreign-key primary-key

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