Insert data in multiple related tables

Question

Insert data in multiple related tables

Asked 4 years, 5 months ago

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)
)

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.

– Jakson Fischer

2020/01/20 at 17:41
2

I think you should rethink your data model.

– anonimo

2020/01/20 at 17:56
@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.

– Marin

2020/01/20 at 18:35
@anonimo, can you explain to me where my data model failed? so maybe you can rethink.

– Marin

2020/01/20 at 18:36
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

2020/01/20 at 19:25
@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?

– Marin

2020/01/20 at 19:28
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

2020/01/20 at 19:34
@bfavaretto, got it. It makes sense what you said. I’ll review my modeling. Thank you so much for your help.

– Marin

2020/01/20 at 19:39

Show 3 more comments

1 answer

Browser other questions tagged mysql foreign-key primary-key

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

by Anderson Imperatori • 56 points · Answer 1 · 2020-01-21T23:56:44+00:00

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:

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